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

 
Gefällt Ihnen der Beitrag?

1 Gedanke zu “Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln

Schreibe einen Kommentar