Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln

Mit den heute vorgestellten Makro-Lösungen können Sie mit unterschiedliche Methoden die letzte genutzte Zeile oder letzte genutzte Spalte in der Exceltabelle ermitteln und in einer Variablen speichern. Diese Funktionen sind besonders dann äußerst wertvoll, wenn Sie bestimmte Vorgänge automatisieren wollen, der Tabellenumfang aber variieren kann.

Nachfolgend erkläre ich anhand von fünf Makros die unterschiedlichen Varianten. Die Varianten 1_1 und 1_2 nutzen dabei die Funktion UsedRange (genutzter Bereich). Die Alternativen Makros der Varianten 2_1 bis 2_3 dagegen prüfen nicht den genutzten Bereich, sondern jeweils nur eine ausgewählte Zeile oder Spalte.

Beide Lösungsansätze haben Vor- und Nachteile. Die geeignete Variante ist deshalb zwingend in jedem Einzelfall
anhand der vorliegenden Voraussetzungen im Tabellenblatt auszuwählen.

UsedRange makierter Bereich mit Makrovarianten

Die Makro-Varianten die den Bereich UsedRange verwenden reagieren empfindlich auf formatierte Zellen.
Auch leere Zellen, die z.B. lediglich mit einer Hintergrundfarbe, einer anderen Schriftgröße oder
einer Rahmenlinie versehen sind, werden in UsedRange mit eingeschlossen.

Sub Version1_1()

Dim lngZeile As Long, lngSpalte As Long

lngZeile = Worksheets("Tabelle1").UsedRange.SpecialCells(xlCellTypeLastCell).Row
lngSpalte = Worksheets("Tabelle1").UsedRange.SpecialCells(xlCellTypeLastCell).Column

Worksheets("Tabelle1").UsedRange.Select

MsgBox "letzte Zeile: " & lngZeile & vbCr & "Letzte Spalte: " & lngSpalte

Worksheets("Tabelle1").Range("A1").Select

End Sub

Im Gegensatz zu Version1_1 wird in Version1_2 nicht die letzte Zelle ermittelt und die Spalte und Zeile dadurch ermittelt, sondern im genutzten Bereich werden die Spalten und Zeilen gezählt! Die Gefahr bei dieser Variante ist, dass oberhalb oder links neben dem genutzten Bereich noch komplette Leerzeilen vorhanden sind.

Sub Version1_2()

Dim lngZeile As Long, lngSpalte As Long

lngZeile = Worksheets("Tabelle1").UsedRange.Rows.Count
lngSpalte = Worksheets("Tabelle1").UsedRange.Columns.Count

Worksheets("Tabelle1").UsedRange.Select

MsgBox "letzte Zeile: " & lngZeile & vbCr & "Letzte Spalte: " & lngSpalte

Worksheets("Tabelle1").Range("A1").Select

End Sub

Die alternativen Makro-Varianten mit der Verwendung von End(xlUp) und End(xlToLeft) berücksichtigen dagegen nur gefüllte Zellen.
Leere, jedoch formatierte Zellen bleiben unberücksichtigt. Im Gegensatz zu UsedRange wird hier aber immer nur eine Zeile/Spalte geprüft.
Nachteil bei diesem Lösungsansatz ist vor allem, dass eine geeignete Zeile bzw. Spalte vorhanden sein muss. Sind in der Tabelle nicht immer alle Zellen gefüllt ist
darauf zu achten, dass eine Zeile/Spalte ausgewählt wird, bei der zwingend in jeder Zeile/Spalte ein Wert steht, da ansonsten
eventuell nicht die Zeile/Spalte am tatsächlichen Tabellenende ermittelt wird.

Die Versionen2_1 bis 2_3 unterscheiden sich nur darin, mit welchen Excel-Versionen sie lauffähig sind.

Public Sub Version2_1()
'Bis Excelversion 2003

Dim lngZeile As Long, lngSpalte As Long

'Letzte gefüllte Zeile in Spalte B
lngZeile = ActiveSheet.Range("B65536").End(xlUp).Row
'Letzte gefüllte Spalte in Zeile 2
lngSpalte = ActiveSheet.Range("IV2").End(xlToLeft).Column

Worksheets("Tabelle1").Rows("2:2").Select
MsgBox "letzte Spalte: " & lngSpalte
Worksheets("Tabelle1").Range("B:B").Select
MsgBox "Letzte Zeile: " & lngZeile

Worksheets("Tabelle1").Range("A1").Select

End Sub

Public Sub Version2_2()
'Ab Excelversion 2007

Dim lngZeile As Long, lngSpalte As Long

'Letzte gefüllte Zeile in Spalte B
lngZeile = ActiveSheet.Range("B1048576").End(xlUp).Row
'Letzte gefüllte Spalte in Zeile 2
lngSpalte = ActiveSheet.Range("XFD2").End(xlToLeft).Column

Worksheets("Tabelle1").Rows("2:2").Select
MsgBox "letzte Spalte: " & lngSpalte
Worksheets("Tabelle1").Range("B:B").Select
MsgBox "Letzte Zeile: " & lngZeile

Worksheets("Tabelle1").Range("A1").Select

End Sub

Public Sub Version2_3()
'Versionsunabhängig in allen Excelversionen lauffähig

Dim lngZeile As Long, lngSpalte As Long

'Letzte gefüllte Zeile in Spalte B
lngZeile = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
'Letzte gefüllte Spalte in Zeile 2
lngSpalte = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

Worksheets("Tabelle1").Rows("2:2").Select
MsgBox "letzte Spalte: " & lngSpalte
Worksheets("Tabelle1").Range("B:B").Select
MsgBox "Letzte Zeile: " & lngZeile

Worksheets("Tabelle1").Range("A1").Select

End Sub

Werden keine individuellen Formatierungen genutzt, ist die Variante 1_1 besonders empfehlenswert.
Für Tabellen mit Formatierungen, die über das Ende der Datenaufstellung hinausgehen können, sind die Versionen 2_x geeigneter.
Zu empfehlen ist insbesondere die Variante 2_3, weil Sie unabhängig von der genutzten Excel-Version funktioniert. Mit Excelversion 2007 wurde die Anzahl der
verfügbaren Zeilen auf 1.048.576, und die Anzahl der verfügbaren Spalten auf 16.384 erhöht. Das Makro in Variante 2_3 arbeitet nicht mit einer fest definierten
Zeilen- und Spaltenzahl, sondern stellt die verfügbaren Zeilen und Spalten über die Funktion „Rows.Count“ (Zeilen zählen) und „Columns.Count“ (Spalten zählen)
jeweils neu fest.

Eine kleine Übungsdatei mit allen fünf Makros können Sie downloaden. Die Ergebnisse werden in einer Message-Box angezeigt und der dabei geprüfte Bereich markiert.

Letzte Zeile und Spalte in Excel ermitteln
letztezeilespalte.xls

Weiterlesen…
Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen
Leere Zeilen in Exceltabellen löschen
Doppelte Zeilen löschen

 
Gefällt Ihnen der Beitrag?

3 Gedanken zu “Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln

  1. Bei allen Lösungen gibt es Besonderheiten. Z.B. Ausgeblendete Spalten. Hab jetzt eine Lösung die auch dies berücksichtigt.

    Option Explicit

    ‚ ——————————————————————-
    ‚ Liefert die letzte verwendete Spalte, auch wenn sie ausgeblendet ist
    ‚ ——————————————————————-
    Function Get_Last_Col(Optional lngZeile As Long = 1, Optional wsh As Worksheet) As Long
    ‚ WE 19-08-16
    Dim lngCol As Long
    Dim lngCount As Long
    Dim lngMaxCol As Long

    If wsh Is Nothing Then Set wsh = ActiveSheet
    lngMaxCol = Columns.Count
    ‚ das Problem ist das wenn es ausgeblendete Spalten gibt, werden diese nicht gesehen
    ‚ der Wert stimmt nur für die letzte sichtbare Spalte
    lngCol = wsh.Cells(lngZeile, lngMaxCol).End(xlToLeft).Column

    Do
    ‚ zählt alle Zellen, 1 rechts neben der angeblich letzten Spalte, die nicht leer sind
    lngCount = Application.WorksheetFunction.CountA( _
    wsh.Range(wsh.Cells(lngZeile, lngCol + 1), Cells(lngZeile, lngMaxCol)))
    ‚ gibt es noch verwendete Zellen dann zähle sie dazu und durlauf die Schleife nochmals
    If lngCount > 0 Then lngCol = lngCol + lngCount

    Loop Until lngCount = 0

    Get_Last_Col = lngCol

    End Function

    ‚ ——————————————————————-
    ‚ Ermittelt die letzte verwendete Zeile einer Spalte
    ‚ wird keine Spalte angegeben so wird Spalte 1 = A verwendet
    ‚ Aufruf mit Zahl oder Buchstaben der Spalte
    ‚ Rückgabe: 0 = Spalte ist absolut leer
    ‚ > 0 = letzte verwendete Zeile
    Function Get_Last_Row(Optional vSpalte As Variant = 1, Optional wsh As Worksheet) As Long
    ‚ WE 19-08-16
    Dim iSpalte As Integer
    Dim lngRow As Long
    Dim lngCount As Long
    Dim lngMaxRow As Long

    If wsh Is Nothing Then Set wsh = ActiveSheet
    ‚ maximale Anzahl der Zeilen in einer Spalte
    lngMaxRow = Rows.Count
    ‚ Spaltenbuchstabe in Zahl umwandeln
    iSpalte = wsh.Cells(1, vSpalte).Column

    ‚ das Problem ist das wenn es ausgeblendete Zeilen gibt, werden diese nicht gesehen
    ‚ der Wert stimmt nur für die letzte sichtbare Zeile
    lngRow = wsh.Cells(lngMaxRow, iSpalte).End(xlUp).Row
    If lngRow = 1 Then
    If Len(wsh.Cells(1, vSpalte)) = 0 Then lngRow = 0
    End If

    Do
    ‚ zählt alle Zellen, 1 unter der der angeblich letzten Zeile, die nicht leer sind
    lngCount = Application.WorksheetFunction.CountA( _
    wsh.Range(wsh.Cells(lngRow + 1, iSpalte), Cells(lngMaxRow, iSpalte)))
    ‚ gibt es noch verwendete Zellen dann zähle sie dazu und durlauf die Schleife nochmals
    If lngCount > 0 Then lngRow = lngRow + lngCount

    Loop Until lngCount = 0

    Get_Last_Row = lngRow

    End Function

    • @Werner Ebner: Vielen Dank für das Feedback. Allerdings ist bei Ihren Funktionen zu beachten, dass Sie gezielt die Zeile oder Spalte für die Funktion benennen müssen, in der die Einträge mit der letzten Zeile oder Spalte vorhanden sind. Wird keine Spalte oder Zeile genannt, wird automatisch in Spalte A und Zeile 1 gesucht. Liegen hier keine Einträge vor, erhält man im schlimmsten Fall sogar als Ergebnis 0. Im Beispiel der Muster-Datei würde Ihre Funktion daher nicht funktionieren.

      Ihre Funktion kann somit auch nur in bestimmten Konstellationen genutzt werden. Aber das ist bei eigentlich allen VBA-Lösungen zu berücksichtigen. Sie als EDV-Coach wissen das natürlich, aber nicht jeder Besucher im Blog dürfte Profi-Programmierer sein. Daher dieser Hinweis für die Leser im Blog.

Schreibe einen Kommentar

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.