Letzte Zeile und letzte Spalte mit VBA ermitteln

Bezogen auf den Beitrag "For"-Loop in VBA kam von einem Leser die Frage, ob das Makro später hinzugefügte Einträge automatisch berücksichtigen kann. In der angesprochenen Schleife (Loop) haben wir einen Vorgang X-mal wiederholt. In diesem Beitrag werden wir nun die letzte Zeile der aktuellen Tabelle durch ein Makro selbst ermitteln lassen, damit die Anzahl der vorzunehmenden Vorgänge automatisch bestimmt wird.  Ich verwende hier die gleiche Beispieltabelle wie bereits für den "For"-Loop:

Beispieldatei

Die letzte Zeile ist also die 7 und unser Ziel ist es, in Spalte B "männlich" oder "weiblich" zu erhalten - je nach Eintrag in Spalte A.

Unser "For"-Loop besteht in dem Fall aus nachfolgenden Code-Zeilen:

For x = 2 To 7
If Cells(x, 1) = "M" Then
Cells(x, 2) = "Männlich"
End If
If Cells(x, 1) = "W" Then
Cells(x, 2) = "Weiblich"
End If
Next

Die Anzahl Wiederholungen der Schleife wurden mittels "For x = 2 To 7" angegeben - also von Zeile 2 bis 7.

Wenn wir jedoch im Laufe der Zeit weitere Werte erfassen, müssten wir auch die "7" in unserem Code anpassen. Hier kommt die Ermittlung der letzten Zeile ins Spiel. Mit diesem Befehl kannst Du die letzte Zeile der Spalte A im aktuellen Tabellenblatt ermitteln:

ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Die letzte Zeile muss jedoch als Variable definiert werden. Nennen wir sie "LetzteZeile". Der vollständige Befehl in VBA zur Ermittlung der letzten Zeile lautet demzufolge:

LetzteZeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Wenn Du nun vor Deinem Loop diese Codezeile stehen hast, kannst Du die Anzahl Wiederholungen mit der Variable "LetzteZeile" angeben:

For x = 2 to LetzteZeile

Erfasse nun einmal weitere Daten in Deiner Excel-Tabelle und teste Dein Makro erneut. Führe den Code auch einmal mit "F8" Schritt für Schritt aus. Wenn Du dann mit der Maus über "LetzteZeile" oder auch über "x" stehen bleibst, siehst Du, was der aktuelle Wert für diese Variablen ist:

MouseHoover

Um die letzte Spalte (beispielsweise in Zeile 1) zu ermitteln, lautet der Befehl wie folgt:

LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column

Solche Variablen zu setzten erlaubt es Dir, Deine Excel-Tabellen viel dynamischer zu gestalten, sodass Deine Makros direkt auf neue Einträge reagieren und Du den effektiven Bereich nicht mehr selbst anpassen musst. Gerade bei komplexeren Programmen wird es immer schwieriger, den Überblick zu behalten und sämtliche solche Bereiche von Hand anzupassen. Hast Du konkrete Anwendungsbeispiele oder Fragen zu diesen Themen? Lass es mich doch in den Kommentaren wissen oder schreib mir via Kontaktformular - ich würde mich freuen, weitere spezifische Anliegen zu behandeln!

4 Gedanken zu “Letzte Zeile und letzte Spalte mit VBA ermitteln

  1. Hallo,

    ich hätte eine Aufgabe, welche ich nicht lösen kann. Ich habe eine Tabelle in den Zeilen mit einer Artikelnummer und in der Spalte mit einem Datum. Als Werte werden die Verkaufswerte angezeigt. Jetzt habe ich das Problem, dass z.B. zum 03.01.2018 unter dem Artikel 1 zwei Werte stehen (Wert alt und neu jeweils in einer eigenen Spalte) und dieser Wert sind auch Gültig für den Zeitraum davor oder danach. Ganz konkret möchte ich, dass das Makro Zeile für Zeile durchläuft (Anzahl der Zeilen sind variabel) und immer die Spalte sucht in dieser der letzte Wert steht. Wenn das Makro diese Zelle gefunden hat, dann soll das Makro diese Zelle kopieren und nach rechts verschieben solange wie in der Überschriftszeile ein Datum steht. Das wäre der erste Teil, zugleich soll das Makro von dieser gefunden Zelle eins nach links gehen dort steht auch ein Wert und diesen nach links kopieren bis er auf eine Zelle mit einem Wert trift. Wenn das passiert, dann muss das Makro eine Zelle nach links gehen und diesen Wert kopieren und wieder nach links einfügen. Schluss soll sein, wenn das Makro die Spalte B erreicht hat. Denn in Spalte A stehen die Artikelnummern.

    Danke schon mal für die Beantwortung meiner Frage.

    Gruß

    Christoph

    • Hallo Christoph
      Danke für Deinen Kommentar. Ich habe kurz versucht, die Tabelle nachzubauen. Es wäre jedoch einfacher, wenn Du mir eine Beispieltabelle zustellen könntest (entweder über das Forum oder via Email). Die Lösung liegt wohl in einem "Loop", der die jeweiligen Zellen prüft und dann innerhalb der Zeile die Werte von rechts nach links transportiert.
      Liebe Grüsse
      Roman

  2. Hallo Roman,

    du erklärst die Codes hier wirklich super.
    Jedoch bin ich absoluter VBA Neuling und habe noch eine Rückfrage 🙂
    Ich möchte meine Werte aus der UserForm stets in die Zeile (z.B. 5) der aktuell aktiven Spalte übertragen. Ggfs. würde auch gehen immer in die Zeile 5 der ersten freien Spalte AB Spalte "I", da alle anderen Spalte davor ggfs. leer sind und bleiben sollen.

    Da Spalte "I" belieb oft kopiert und analog ausgefüllt werden sollen kann, ist es wichtig, dass es dynamisch bleibt. Sprich wenn ich die UserForm über Spalte "J" ausfülle, es mir die Daten dann in Spalte "J" schreibt.

    Ich hoffe man versteht was ich meine und es gibt Abhilfe für mein Problem 🙂

    Tausend Dank bereits im Voraus und Grüße,
    Madeleine

    • Hallo Madeleine

      Um den Wert in die aktuell aktive Spalte zu übertragen, würden Dir folgende Codezeilen dienen:

      Dim AktuelleSpalte As Integer
      AktuelleSpalte = ActiveCell.Column

      Cells(5, AktuelleSpalte) = WertAusUserForm

      Zuerst definierst Du die Variable (Integer) "AktuelleSpalte" - mit "ActiveCell.Column" erhältst Du die Zahl der Spalte (Spalte C wäre beispielsweise 3). Danach schreibst Du den Wert aus Deinem UserForm in die Zeile 5 der aktuellen Spalte.

      Falls Du sicherstellen möchtest, dass der Benutzer sich in der Spalte "I" oder weiter rechts in der Datei befindet, dann wäre die nachfolgende If-Überprüfung spannend, da Du sicherstellst, dass man sich in der Spalte I oder weiter hinten befindet (I ist der 9. Buchstabe im Alphabet).

      If AktuelleSpalte >= 9 Then
      ...
      End If

      Um die nächste freie Spalte zu ermitteln, könnte Dir eine Codezeile wie die nachfolgende helfen:
      FreieSpalte = Range("I5").End(xlToRight).Column

      Bitte experimentiere mit dem Startpunkt etwas - die obige Codezeile bewirkt dasselbe, wie wenn Du die Zelle "I5" markierst und dann mit der Kombination "CTRL (Strg) + Pfeil-Nach-Rechts" navigierst. Allenfalls müsstest Du dann noch mit -1 korrigieren oder eine Sicherheit einbauen, dass der Cursor nicht in der Spalte 16384 landet (= Spalte XFD am "Ende" von Excel). Hier hilft Dir ebenfalls ein Statement:

      If FreieSpalte = 16384 Then
      FreieSpalte = 9
      End If

      So setzt Du den Spaltenzähler auf die Spalte I, um dort den gewünschten Eintrag zu machen.

      Füge allenfalls diese Codezeile oberhalb ein, damit der Bildschirm bei der Makro-Ausführung nicht flackert:
      Application.ScreenUpdating = False

      ... und zu unterst im Code dann wieder aktivieren mit:
      Application.ScreenUpdating = True

      Lass es mich gerne wissen, ob alles funktioniert hat!

      Viele Grüsse
      Roman

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

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