VBA

Letzte Zeile und letzte Spalte mit VBA ermitteln

Roman VBA (Makros) , , , , , , , , , ,

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!

Suchmaschine programmieren

Roman Formeln, VBA (Makros) , , , , , , ,

Sobald eine Excel-Tabelle viele Einträge hat, sind Filter beinahe unumgänglich. Man kann einzelne Begriffe selbstverständlich rasch mittels der Such-Funktion "CTRL + F" finden. Wir wollen hier jedoch in VBA unsere eigene Suchmaschine programmieren, um wiederkehrend unsere Datenbank nach einem gewünschten Suchkriterium zu durchsuchen. Unser fertiges Produkt soll somit nur diejenigen Zeilen anzeigen, welche auch dieses Kriterium enthalten. In diesem Beitrag programmieren wir eine solche Suchmaschine für folgende Musik-Datenbank:

Datenbank

Vorbereitungsschritte

Wir fügen nun oberhalb der Überschrift eine neue Zeile 1 ein. In "A1" schreiben wir "Suchkriterium:" und die Zelle "B1" markieren wir farbig - dort soll jeweils unser Suchbegriff eingegeben werden. In "C1" fügen wir ein Textfeld ein und schreiben "Suche starten!" - dies wird unser Button, um das Makro später auszulösen. Damit unser Filter auf das Suchkriterium abgestimmt werden kann, müssen wir in Spalte D die nachfolgende Formel einsetzen. Für die Zeile Nummer 3 (Zelle "D3") lautet diese für's Erste:

=WVERWEIS($B$1;A3:C3;1;FALSCH)

Du kannst nun einmal einen Suchbegriff eingeben und das Resultat für die Formel "WVERWEIS(...)" betrachten:

WVERWEIS

Ich empfehle Dir, die Suchbegriffe jeweils mit Sternchen (*) einzugeben, damit Du sicher alle Resultate angezeigt bekommst, welche den Begriff enthalten.

Die Formel "WVERWEIS(...)" müssen wir noch leicht anpassen, um den Filter besser anwenden zu können. Wir wenden hierzu zusätzlich die Formel "WENNFEHLER" und schreiben:

=WENNFEHLER(WVERWEIS($B$1;A3:C3;1;FALSCH);0)

Du siehst nun: Überall, wo vorhin "#NV" war, steht nun "0". Die Fehlermeldung "#NV" hatte uns lediglich angezeigt, dass in der entsprechenden Zeile nichts dem Suchkriterium entspricht. Die "0" wird für uns im Makro jedoch viel einfacher auszugrenzen sein.

Automatisch filtern mit VBA

Wir setzen nun den Filter auf Zeile zwei (unsere Überschrift) und beginnen, ein Makro aufzuzeichnen. Sobald der Rekorder gestartet ist, filtern wir sämtliche "0" heraus und stoppen die Aufnahme bereits wieder. Folgender Code wurde in der Zwischenzeit aufgezeichnet:

ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=4, Criteria1:="Pop"

Allerdings entspricht dies nicht ganz dem, was wir eigentlich wollten. Die Intention war es ja, alle "0" auszufiltern. Deshalb müssen wir den letzten Teil des Filterkriteriums ("Criteria1:="Pop") anpassen zu:

Criteria1:="<>0"

Wir können nun ein anderes Suchkriterium eingeben und unser Makro ausführen - es funktioniert wie gewünscht! Zuletzt können wir das Makro unserem Textfeld ("Suche starten!") zuweisen. Mittels Rechtsklick auf das Textfeld → "Makro zuweisen" kannst Du dein zuvor aufgezeichnetes Makro selektieren.

"Message-Box" über die Anzahl Treffer

Nehmen wir uns noch zwei ästhetischen Punkten an: Wir können die Spalte D problemlos ausblenden, das Makro wird nach wie vor funktionieren. Desweiteren möchten wir eine "Message-Box" einrichten, die uns jeweils die Anzahl der gefundenen Treffer angibt. Wir geben eine weitere Codezeile ein:

AnzahlTreffer = Application.WorksheetFunction.Subtotal(3, Range("A:A"))-2

Die Variable "AnzahlTreffer" umfasst das Resultat der Funktion "TEILERGEBNIS" unter der Verwendung von "ANZAHL2". Wir subtrahieren "2", um die beschriebenen Felder "A1" und "A2" abzuziehen. Nun können wir die nächste Codezeile einfügen:

MsgBox ("Insgesamt " & AnzahlTreffer & " Treffer gefunden.")

 ... und dies ist unser Resultat:

 MsgBox

"For"-Loop in VBA

Roman VBA (Makros) , , , , , , , , , , ,

Bei Makros kann man jeden Befehl einzeln programmieren oder aufzeichnen. In einer ersten Einführung in die VBA-Umgebung habe ich Dir gezeigt, wie Du mit dem Makro-Rekorder arbeiten kannst und schnell zu mehr Code kommst. Sich wiederholende Befehle kann man selbstverständlich alle einzeln in Code schreiben. Es ist jedoch deutlich übersichtlicher, wenn Du Wiederholungen mit sogenannten Loops (Schleifen) programmierst. In diesem Beitrag möchte ich Dir die "For"-Schleife zeigen.

Mit diesem Loop kannst Du einen Befehl x-beliebig ausführen lassen. Mit dem nachfolgenden Code wird im Bereich A1:A10 jeweils "Mein erster Loop" geschrieben:

For x = 1 To 10
Cells(x, 1) = "Mein erster Loop"
Next

Betrachten wir die einzelnen Bestandteile genauer: Der Loop beginnt mit "For". Danach verwenden wir X als Variable, beginnend bei 1 bis 10. In der zweiten Code-Zeile verwenden wir "Cells" - also Zelle. In Zelle "x, 1" soll unser Text geschrieben werden. Wichtig zu beachten ist, dass in "Cells" die Reihenfolge gegenüber Excel umgekehrt ist - es beginnt hier mit der Zeile (Row), danach wird erst nach der Spalte (Column) gefragt. In Excel ist zuerst die Spalte und dann die Zeile anzugeben (beispielsweise A1). Der Befehl "Next" erweitert das X um eins und die Schlaufe wird erneut durchlaufen. Probier es mal aus und führe Dein Makro mit F8 Schritt für Schritt aus. Du kannst mit dem Cursor auf X bleiben, dann zeigt es Dir jeweils den aktuellen Wert der Variable an. Zu Beginn ist dies "x = Leer", danach nimmt es die Werte 1 bis 10 an.

Weitere Anwendungsbereiche

Abhängig von Deinem spezifischen Makro kann diese For-Schleife in unterschiedlicher Form zur Anwendung kommen. Im ersten Beispiel oben hast Du Dich auf die Zeile bezogen. Dasselbe funktioniert natürlich auch mit der Spalte [Cells(1, x)]. Eine weitere Möglichkeit besteht darin, eine Zelle auf dessen Wert zu überprüfen. In der nachfolgenden Datenbank soll das Geschlecht jeweils ausgeschrieben werden (W = Weiblich; M = Männlich).

For-Schleife

Dazu schreiben wir folgenden Code:

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 For-Schleife beginnt diesmal bei X = 2. Danach findet eine Überprüfung mittels "If-Then" statt. Pro Durchlauf habe ich jeweils nach "M" und anschliessend nach "W" gesucht - einfacher wäre die Verwendung von "If-Then-Else", also "Wenn-Dann-Sonst". Ich decke diesen Fall nicht in diesem Beitrag ab, Du kannst das aber gerne selbst ausprobieren!

Den letzten Fall, den ich noch aufzeigen möchte, ist das Entfernen von Zeilen. Nehmen wir an, wir möchten nur die weiblichen Personen angezeigt haben. Unsere Überprüfung soll also nach "M" suchen und in dem Fall die gesamte Zeile entfernen. Der Code dazu lautet wie folgt:

For x = 2 To 7
If Cells(x, 1) = "M" Then
Rows(Cells(x, 1).Row).Delete
x = x - 1
End If
Next

Der Check in den ersten beiden Zeilen bleibt gleich wie im oberen Beispiel. Danach wird die Zeile der Zelle "x, 1" entfernt. Da sämtliche unteren Zeilen nach oben rücken, müssen wir in dem Fall x unbedingt wieder um 1 zurücksetzen, ansonsten werden einzelne Zeilen ungewollt übersprungen. Je nach dem, wie Deine Daten angeordnet sind, kannst Du mit "x - 1" oder "x + y" y-beliebig viele Zeilen überspringen, bevor Dein Loop wieder zur Anwendung kommt. Deine Variable in der For-Schleife bezieht sich dann nicht auf die Zeile selbst, sondern auf den Fall Nummer x. Dein Loop verarbeitet somit x-beliebige Fälle - und den Code dazu brauchst Du nur ein einziges Mal zu erfassen.

Natürlich könnten sämtliche dieser Befehle leicht in Excel mittels Formel "WENN", dem Setzen eines Filters und dem Löschen der jeweiligen Zeilen vorgenommen werden. Falls es sich aber um eine grosse Tabelle handelt, oder ein wiederkehrender Prozess vorliegt, kann das Makro abgespeichert und jederzeit aufgerufen werden - es lediglich ein initialer Aufwand zur Programmierung notwendig, danach entstehen erhebliche Zeitgewinne und das Fehlerpotenzial wird minimiert (sofern alles korrekt programmiert wurde).