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