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.
Teil 1: Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln
Teil 2: Leere Zeilen in Exceltabellen löschen
Teil 3: Doppelte Zeilen löschen
Teil 4: Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen
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.
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
Eine Besonderheit gilt es zu beachten, wenn das Tabellenblatt einen Autofilter beinhaltet und mit diesem die Daten gerade gefiltert sind. Die vorherigen Makros suchen die letzte Zeile immer nur unter Berücksichtigung der eingeblendeten Zeilen. Bei der Nutzung im Zusammenhang mit gefilterten Daten durch einen Autofilter ist eine weitere Anpassung wie im Beispiel der Version2_4 erforderlich. Hier wird zunächst auf herkömmlichen Weg die letzte Zeile identifiziert und anschließend mit der letzten Zeile des Autofilterbereichs verglichen.
Public Sub Version2_4()
Dim lngZeile As Long
lngZeile = Worksheets("Tabelle1").Range("B" & Rows.Count).End(xlUp).Row
If Worksheets("Tabelle1").AutoFilterMode = True Then
If lngZeile < Worksheets("Tabelle1").AutoFilter.Range.Rows.Count + Worksheets("Tabelle1").AutoFilter.Range.Row - 1 Then
lngZeile = Worksheets("Tabelle1").AutoFilter.Range.Rows.Count + Worksheets("Tabelle1").AutoFilter.Range.Row - 1
End If
End If
MsgBox "Letzte Zeile: " & lngZeile
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. Für Tabellenblättern mit Autofiltern ist ggf. noch die erforderliche Anpassung aus Variante 2_4 zu berücksichtigen.
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.
Weiterlesen…
Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen
Leere Zeilen in Exceltabellen löschen
Doppelte Zeilen löschen
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
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
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.
Wie jede Funktion, tut meine das wozu der Entwickler sie geschaffen hat.
Wenn ich die letzte verwendete Zelle möchte kann ich beides nutzen.
Ich habe auch eine Sonderform der Funktion die mir von einer beliebigen Reihe an Spalten die wirklich aller letzte Zeile bring.
Würde die Funktionalität von Excel ausreichen und immer sauber die letzte verwendete Zelle zeigen, gäbe es keine Notwendigkeit dies selbst zu tun.
Und das mit der 0 ist nicht der schlimmsten Fall, es ist so gewollt.
Aber sie haben natürlich nicht unrecht wenn sie zu Bedenken geben das nicht jeder der einen Knochen vor die Füße bekommt, in der Lage ist dies richtig zu deuten.
Aber wenn ich zu einem Problem einen Knochen aus meiner Trickkiste ziehe werd ich keine Abhandlung darüber mitliefern was dieser Knochen kann und an welche Stelle des Dinos er gehört.
@Werner Ebner: Ich finde es super, dass Sie sich mit einem Beispielcode an der Diskussion beteiligen, aber dann sollte man so fair und ehrlich sein und auch noch die Vor- und Nachteile darlegen. Sonst hilft der Kommentar den Lesern im Blog nicht weiter. Wenn aber das eigentliche Ziel Ihres Kommentars nicht die Unterstützung bei der Suche nach einer sinnvollen Lösung war, sondern für die eigene Webseite einen Backlink zu erhalten, dann ist das sehr bedauerlich. Dann brauchen Sie aber auch nicht verschnupft reagieren, wenn ich diese Informationen ergänze.
Wer es noch genauer machen will, kann ja eine weitere Schleife hinzufügen (Bsp. Spalten):
If wsh Is Nothing Then Set wsh = ActiveSheet
For lngZeile = 1 to Rows.Count
…
…
Get_Last_Col = WorksheetFunction.Max(lngCol, Get_Last_Col)
Next lngZeile
Aber das Programm benötigt dann eine kleine Ewigkeit!