Startseite

Tabellengröße ermitteln

Zunächst einmal zeige ich, wie man die Größe einer Tabelle mit Hilfe einer Do Loop While Schleife ermitteln kann. Wie fast immer, gibt es auch für diesen Zweck eine andere Lösung. Diese werde ich am Ende der Seite vorstellen.
Sub tabellenlaenge_ermitteln()
Dim z%
z = 1
Do While Cells(z, 1) <> ""
    z = z + 1
Loop
z = z - 1
End Sub

Der Unterschied für die Ermittlung von Länge und Breite der Tabelle(ninhalte) beschränkt sich auf die Cells-Anweisung in der dritten Zeile. Eine Kombination beider Quelltexte ist möglich. Leere Zellen in der ersten Zeile oder in der ersten Spalte, verursachen ein falsches Ergebnis, da dort der Schleifenlauf abgebrochen wird. (Bedingung <>"")
Sub tabellenbreite_ermitteln()
Dim sp%
sp = 1
Do While Cells(1, sp) <> ""
    sp = sp + 1
Loop
sp = sp - 1
End Sub



Zeile Befehl Erläuterung Näheres
1 Sub tabellenlaenge_ermitteln() Name des Makros. Kann wahlfrei festgelegt werden. Groß- und Kleinschreibung möglich.
Unterstrich zwischen den einzelnen Wörtern ist erforderlich!
 
2 z = 1 Startwert für Schleife. Hier Zeile 1  
3 Do While Cells(z, 1)<>"" Beginn der Schleife. Sie wird solange durchlaufen, bis eine Zelle ohne Inhalt gefunden wird.  
4 z = z + 1 Erhöht bei jedem Durchlauf den Zähler um den Wert 1  
5 Loop "Wendepunkt" für die Schleife. Solange die Bedingung in Zeile 3 erfüllt ist, erfolgt von hier ein neuer Schleifendurchlauf.  
6 z = z - 1 Korrektur des oben ermittelten Wertes, da der Schleifenzähler vor dem Wendepunkt liegt.  
7 End Sub Beendet das Makro  
Mit leichten Veränderungen ist es auch möglich, mit diesem Beispiel Tabelleninhalte auszulesen. Das aber an andere Stelle.
Hier die oben versprochene alternative Lösung:
LetzteZelle = Range("A1").End(xlDown).Row
Diese Zeile übergibt die Nummer der letzten gefüllten Zelle in der Spalte A an die Variable 'LetzteZelle' , andere Namen sind natürlich zulässig.
Mit einer ähnlichen Anweisung kann auch die Breite der Tabelle ermittelt werden:
LetzteSpalte = Rows(1).End(xlToRight).Column
Das ist natürlich wesentlich kürzer, als der Weg über die Schleife. Es war aber mein Anliegen, ein praktisches Beispiel für die Verwendung einer Do Loop While Schleife zu geben.
Bei beiden Vorgehensweisen besteht die Gefahr, falsche Werte zu erhalten. Das ist bei den Do While Loop Schleifen ebenso der Fall, wie bei den Formeln, wenn sich in der Tabelle leere Zellen befinden. Das wird dann als Ende der Zeilen bzw. Spalten interpretiert. Bei den Formeln kommt es auch zu Fehlern, wenn eine größere Tabelle wieder verkleinert wurde (z.B. Anzahl der Zeilen). Hier bekommt man erst korrekte Werte wenn die Tabelle zwischendurch gespeichert wurde.
Das Problem mit den leeren Zellen läßt sich bei den Formeln aber umgehen. Beide Formeln suchen das Tabellenende von der Zelle A1 in Richtung Tabellenende. Wenn man das umdreht, also vom Rand des Tabellenblattes aus die erste Zelle mit Inhalt sucht, dann ist dieses Problem nicht da.
Die Formeln sehen dann so aus:
LetzteZelle = Range("A" & Rows.Count).End(xlUp).Row
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column
Bleibt der Hinweis, daß bei den Beispielen jeweils die Länge bzw. Breite der Tabelle in der Spalte A bzw. Zeile 1 ermittelt wird. Natürlich kann das auch jede andere Zeile oder Spalte sein, es müssen dann die entsprechenden Werte in die Formel(n) geschrieben werden.
Es gibt aber auch noch eine Möglichkeit die letze gefüllte Zelle unabhängig von einer bestimmten Spalte zu suchen. Das geht mit dieser Formel:
Letzte Zelle = ActiveCell.SpecialCells(xlLastCell).Row (bezogen auf Zeile)
Letzte Zelle = ActiveCell.SpecialCells(xlLastCell).Column (bezogen auf Spalten)

Weitere Makrolösungen

Unterprogramme

Hier ein weiterer Lösungsansatz mit je einem Makro für die letzte gefüllte Zeile, bzw. letzte gefüllte Spalte. Er nutzt die Find-Methode und hat den Vorteil, dass leere Zellen das Ergebnis nicht beeinträchtigen, so wie das auch bei der letzten Lösung der Fall ist. Im Gegensatz dazu kann diese Lösung auch auf nicht aktive Tabellenblätter angewendet werden.
Option Explicit

Sub leZeile()
    Dim Adresse As String
    Dim lngZeile As Long
    Adresse = Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Find(What:="*", After:=Cells(1, Columns.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Address
    lngZeile = CInt(Range(Adresse).Row)
    MsgBox lngZeile
End Sub

Sub leSpalte()
    Dim Adresse As String
    Dim lngSpalte As Long
    Adresse = Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Find(What:="*", After:=Range("A" & Rows.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Address
    lngSpalte = CInt(Range(Adresse).Column)
    MsgBox lngSpalte
End Sub

Benutzerdefinierte Funktionen

Aus den beiden Makros habe ich diese benutzerdefinierten Funktionen abgeleitet.
Option Explicit

Function leZeile() As Long
leZeile = CLng(Range(Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Find(What:="*", _
        After:=Range("A" & Rows.Count), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Address).Row)
End Function

Function leSpalte() As Integer
leSpalte = CInt(Range(Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Find(What:="*", _
        After:=Cells(1, Columns.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:= _
        xlByColumns, SearchDirection:=xlPrevious).Address).Column)
End Function

Quelltexte eingefügt mit: Excel Code Jeanie