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.

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.

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

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.

Letzte Zeile und Spalte in Excel ermitteln
letztezeilespalte.xlsm

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?

6 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

    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

    Antworten
    • @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.

      Antworten
  2. 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.

    Antworten
    • @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.

      Antworten
  3. 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!

    Antworten

Schreibe einen Kommentar

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