Dynamischen Bereich summieren & Nicht Zahlen markieren
In diesem vierten Teil meiner Excel-VBA-Tutorialreihe bauen wir auf den Grundlagen der vorherigen Videos auf und machen einen kleinen, aber sehr praxisnahen Schritt nach vorne: Wir summieren Werte nicht mehr aus einem festen Bereich wie A1, sondern dynamisch bis zur letzten gefüllten Zeile in Spalte A.
Das Makro prüft dabei jede Zelle im Bereich, addiert nur echte Zahlen zur Summe und ignoriert alles, was keine Zahl ist. Zusätzlich zählen wir mit, wie viele Zellen übersprungen wurden – und markieren diese optional direkt im Tabellenblatt, damit man sofort sieht, welche Eingaben nicht zur Berechnung gepasst haben. Am Ende wird die Summe in B1 ausgegeben und bei Bedarf erscheint ein Hinweisfenster mit der Anzahl ignorierter Zellen.
Wichtig: Dieses Tutorial ist Teil 4 der Serie und setzt das Grundlagenwissen aus den ersten Teilen voraus (z.B. Makros starten, Module/Prozeduren, Variablen, Option Explicit). Wenn du neu in VBA bist, schau dir bitte zuerst Tutorial 1 an – dann kannst du hier ohne Lücken mitmachen und verstehst jeden Schritt.
Voraussetzung: Bitte zuerst Teil 1 anschauen (Entwicklertools aktivieren, Modul anlegen, Option Explicit). Den Link findest du hier:
Excel VBA Kurs – Teil 01: Start mit Hello World – Modul, Makro, Option Explicit
⬇️ Den vollständigen Code kannst du weiter unten über den Downloadlink herunterladen.
Achtung:
Benutzt zum Üben eine separate Tabelle für Testzwecke oder eine Kopie des Originals damit Euch keine Daten verloren gehen.
Fertiger Aufbau
Vorschaubild dynamischen Bereich summieren Nicht-Zahlen markieren
Code Zeile für Zeile erklärt
Erzwingt, dass jede Variable vorher mit Dim deklariert werden muss. Tippfehler bei Variablennamen werden so sofort als Fehler erkannt.
Start einer öffentlichen Prozedur (Makro), die du aus Excel heraus starten kannst. Sie heißt „SummeBereich_Dynamisch“.
Legt die Variable ws an. Sie wird später auf ein Tabellenblatt zeigen.
Legt lastRow an. Darin wird später die Nummer der letzten gefüllten Zeile in Spalte A gespeichert.
Legt rng an. Diese Variable wird später den zu prüfenden Zellbereich (z.B. A1:A37) enthalten.
Legt summe an. Hier kommt später die berechnete Summe rein (auch mit Dezimalstellen möglich).
Legt ignoriert an. Hier wird gezählt, wie viele Zellen keine Zahl enthalten (aber nicht leer sind).
Würde das aktuell angeklickte Blatt verwenden (wenn man die nächste Zeile entkommentiert).
Würde ws auf das aktuell aktive Blatt setzen.
Würde das aktive Blatt der Arbeitsmappe verwenden, in der der Code gespeichert ist.
Würde ws auf das aktive Blatt in ThisWorkbook setzen.
Würde ein Blatt über den sichtbaren Tab-Namen auswählen.
Würde ws auf das Blatt setzen, dessen Tab-Name genau „Tabelle1“ ist.
Verwendet den Blatt-Codenamen (Objektname), der sich nicht ändert, wenn man den Tab umbenennt.
Tabellenobjekt im VBA Projekt Explorer
Erklärung, wo man Codename und Tab-Name im VBA-Editor sieht.
Derzeit aktiviert
Setzt ws auf das Blattobjekt mit dem Codenamen Tabelle1. Das ist sehr robust, weil es unabhängig vom Tab-Namen ist.
Es wird geprüft, ob in Spalte A überhaupt Inhalte vorhanden sind.
Zählt alle nicht-leeren Zellen in Spalte A. Wenn das Ergebnis 0 ist, ist die Spalte komplett leer und der Code springt in den If-Block.
Zeigt eine Meldung an: Text „In Spalte A sind keine Werte vorhanden.“, mit Warnsymbol und dem Fenstertitel „Abbruch“.
Beendet das Makro sofort, damit danach nichts weiter ausgeführt wird.
Beendet den If-Block.
Jetzt wird bestimmt, wie weit der Bereich nach unten geht.
Geht in Spalte A ganz nach unten (letzte mögliche Zeile), springt dann wie Strg+Pfeil hoch zur letzten gefüllten Zelle und speichert deren Zeilennummer in lastRow.
Setzt rng auf den Bereich von A1 bis zur Zelle in Spalte A mit der Zeilennummer lastRow (z.B. A1:A37).
Vorherige gelbe Markierungen werden entfernt, damit man nur die aktuellen sieht.
Entfernt Hintergrundmuster/Füllung im gesamten Bereich rng (setzt die Zellfüllung praktisch zurück).
Jetzt wird die Summe berechnet und die ignorierten Zellen werden gezählt.
Ruft die Function auf, übergibt den Bereich rng, übergibt ignoriert als ByRef (wird darin gesetzt) und True zum Markieren. Der Rückgabewert (Summe) landet in summe.
Die Summe wird ins Blatt geschrieben.
Schreibt die berechnete Summe in die Zelle B1 des ausgewählten Tabellenblatts.
Prüft, ob es mindestens eine nicht-leere Zelle gab, die keine Zahl war.
Zeigt eine Info-Meldung an, z.B. „3 Zelle(n) wurden ignoriert…“. ignoriert & hängt die Zahl vorne an den Text.
Beendet den If-Block.
Beendet das Makro SummeBereich_Dynamisch.
Start einer privaten Function, die einen Double-Wert zurückgibt. rng ist der zu prüfende Bereich, ignoriert wird als ByRef nach außen zurückgegeben, markieren ist ein True/False-Schalter.
Legt c an. Das wird in der Schleife jeweils eine einzelne Zelle aus dem Bereich rng sein.
Legt eine lokale Variable summe an, in der innerhalb der Function aufaddiert wird.
Startwert der Summe auf 0 setzen.
Startwert der ignorierten Zellen auf 0 setzen (wird nach außen zurückgegeben, weil ByRef).
Start einer Schleife: gehe jede einzelne Zelle im Bereich rng nacheinander durch und lege sie in c ab.
Prüft, ob die Zelle wirklich leer ist. Wenn ja, wird nichts gemacht.
Kommentar: Leere Zellen werden übersprungen.
Wenn die Zelle nicht leer ist: Prüft, ob der Zellinhalt eine Zahl ist.
Wandelt den Zellwert sicher in Double um und addiert ihn zur laufenden Summe.
Trifft zu, wenn die Zelle nicht leer ist, aber auch keine Zahl enthält (z.B. Text).
Erhöht den Zähler für ignorierte Zellen um 1.
Prüft, ob der Schalter markieren auf True steht.
Färbt die aktuelle Zelle gelb, damit man sieht, welche Einträge ignoriert wurden.
Beendet den If-Block für das Markieren.
Beendet den If-Block der Zellprüfung (leer / Zahl / sonstiges).
Nimmt die nächste Zelle aus rng.Cells und wiederholt die Schleife, bis alle Zellen geprüft wurden.
Setzt den Rückgabewert der Function auf die berechnete Summe.
Beendet die Function.
Download Dateien:
Alle Inhalte und Codebeispiele auf dieser Seite dienen ausschließlich zu Lern- und Demonstrationszwecken. Die Anwendung erfolgt auf eigenes Risiko. Ich übernehme keine Gewähr für Richtigkeit, Vollständigkeit oder Eignung für einen bestimmten Zweck und keine Haftung für direkte oder indirekte Schäden (z. B. Datenverlust), die durch das Ausführen, Verwenden oder Anpassen des Codes entstehen. Bitte erstelle vorab ein Backup und teste den Code zunächst in einer separaten Testdatei, bevor du ihn in produktiven Arbeitsmappen einsetzt.
Download Dynamischen-Bereich-summieren-und-Nicht-Zahlen-markieren Excel Datei mit Makro
Download Dynamischen-Bereich-summieren-und-Nicht-Zahlen-markieren als Text zum Kopieren
Video zu diesem Tutorial: