🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA SpecialCells in Excel — Leere Zellen, sichtbare Zellen und Konstanten auswählen (und warum es einen Fehler wirft, wenn es nichts findet)

|

VBA SpecialCells in Excel — Leere Zellen, sichtbare Zellen und Konstanten auswählen (und warum es einen Fehler wirft, wenn es nichts findet)

TL;DR — SpecialCells lässt Excel die Zellen nach Typ auswählen statt nach Adresse: alle leeren Zellen, alle sichtbaren Zellen, alle Formeln, alle Konstanten. Es ist der Code-Zwilling von Start → Suchen und Auswählen → Inhalte auswählen. Die wichtigste Regel: Wenn es nichts findet, gibt es keinen leeren Bereich zurück — es wirft den error 1004 „Es wurden keine Zellen gefunden“. Ein ungeschützter Aufruf ist also eine Zeitbombe. Umschließen Sie ihn immer:

Dim blanks As Range
On Error Resume Next
Set blanks = ThisWorkbook.Worksheets("Data").Range("A2:A1000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not blanks Is Nothing Then blanks.Value = 0   ' laeuft nur, wenn tatsaechlich leere Zellen gefunden wurden

Jeder Bezug, den Sie bisher gebaut haben, beschreibt ein Rechteck — Range("A1:D100"), Cells(r, c), ein Block, den Resize oder CurrentRegion herausschneidet. Doch echte Arbeit will selten ein Rechteck. Sie will alle leeren Zellen, um sie zu füllen, nur die sichtbaren Zeilen nach einem Filter, nur die Formeln, um sie zu sperren. SpecialCells ist, wie Sie danach fragen: Sie beschreiben nicht länger eine Adresse, sondern welche Art von Zelle Sie wollen. Diese Anleitung ruht auf einer Idee — SpecialCells ist ein Filter, der einen Bezug zurückgibt, und „nichts gefunden“ ist ein Fehler, kein leeres Ergebnis. Verinnerlichen Sie das, und jede weitere Falle verschwindet.

Was Sie lernen

  • Das mentale Modell — SpecialCells wählt Zellen nach Typ, die Code-Fassung von „Inhalte auswählen“
  • Die wichtigste Regel — es wirft den error 1004, wenn nichts passt, also sichern Sie jeden Aufruf ab
  • Die Zelltypen, die Sie wirklich brauchen — leere, sichtbare, Konstanten, Formeln, letzte Zelle
  • Das Muster zum Leerfüllen — xlCellTypeBlanks plus eine einzeilige Formel
  • Das Muster zum Kopieren sichtbarer Zeilen — xlCellTypeVisible nach einem AutoFilter (der Retter der Zeilenzahl)
  • Warum das Ergebnis oft nicht zusammenhängend ist (mehrere Areas) und was das ändert

Das mentale Modell: SpecialCells wählt Zellen nach Typ, nicht nach Adresse

Range und Cells beantworten die Frage welche Zelle mit einem Ort — einem String oder zwei Zahlen. SpecialCells beantwortet eine andere Frage: welche Zellen einer bestimmten Art? Sie übergeben ihm einen zu durchsuchenden Bereich und eine Typkonstante, und Excel durchsucht diesen Bereich und gibt einen Bezug auf jede Zelle zurück, die dazu passt:

Dim used As Range: Set used = ActiveSheet.UsedRange
used.SpecialCells(xlCellTypeFormulas).Interior.Color = vbYellow   ' jede Formel hervorheben

Wenn Sie je Inhalte auswählen genutzt haben (F5 drücken, dann Inhalte), ist das genau dieser Dialog in Code — „Konstanten“, „Formeln“, „Leerzellen“, „Nur sichtbare Zellen“ sind dieselben Optionen. Die Stärke liegt darin, dass Excel das Durchsuchen übernimmt: Sie durchlaufen nie das Blatt und fragen „ist diese hier leer?“ — Sie fragen alle leeren Zellen auf einmal ab, und Excel gibt sie als einen einzigen (oft seltsam geformten) Bereich zurück.

Die wichtigste Regel: es wirft einen Fehler, wenn es nichts findet

Hier ist die Zeile, die aus einem funktionierenden Makro einen Absturz macht. Wenn keine Zelle im durchsuchten Bereich zum Typ passt, gibt SpecialCells keinen leeren Bereich zurück — es wirft den error 1004, „Es wurden keine Zellen gefunden.“ Ein Bereich ohne leere Zellen, durch SpecialCells(xlCellTypeBlanks) geschickt, bringt Ihr Makro auf der Stelle zum Stehen:

' FRAGIL - stuerzt mit error 1004 an dem Tag ab, an dem es keine leeren Zellen gibt:
Range("A2:A1000").SpecialCells(xlCellTypeBlanks).Value = 0

Das ist so gewollt, kein Bug: SpecialCells behandelt „kein Treffer“ als Ausnahmezustand und nicht als leeres Ergebnis. Das heißt, die sichere Form ist nicht optional — sie ist die einzige richtige. Der dreiteilige Schutz lautet: Fehlerbehandlung einschalten, den Aufruf ausführen, sie wieder ausschalten, dann auf Nothing prüfen:

Dim hits As Range
On Error Resume Next
Set hits = Range("A2:A1000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0                       ' das Verschlucken von Fehlern sofort wieder beenden
If hits Is Nothing Then
    MsgBox "No blank cells to fill."
Else
    hits.Value = 0
End If

On Error Resume Next deckt nur die eine riskante Zeile ab; On Error GoTo 0 stellt die normale Fehlermeldung gleich danach wieder her, damit Sie andere Bugs nicht stillschweigend übergehen (siehe VBA On Error). Lassen Sie den Schutz weg, ist Ihr Makro eine Zeitbombe, die auf jeder Testdatei mit leeren Zellen läuft und bei der ersten sauberen hochgeht.

Die wichtigen Zelltypen

SpecialCells(Type, [Value]) nimmt eine Typkonstante, und eine Handvoll deckt fast alles ab:

rng.SpecialCells(xlCellTypeBlanks)         ' leere Zellen im Bereich
rng.SpecialCells(xlCellTypeVisible)        ' Zellen, die nicht durch Filter oder ausgeblendete Zeilen verborgen sind
rng.SpecialCells(xlCellTypeConstants)      ' getippte Werte (Zahlen, Text) - keine Formeln
rng.SpecialCells(xlCellTypeFormulas)       ' Zellen, die eine Formel enthalten
rng.Cells.SpecialCells(xlCellTypeLastCell) ' die untere rechte Ecke des benutzten Bereichs

xlCellTypeConstants und xlCellTypeFormulas nehmen ein optionales zweites Argument, um nach Ergebnistyp einzugrenzen — SpecialCells(xlCellTypeFormulas, xlErrors) greift nur die Formeln, die aktuell einen Fehler zurückgeben, was der schnellste Weg ist, jedes #REF! oder #DIV/0! auf einem Blatt zu finden:

Dim bad As Range
On Error Resume Next
Set bad = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not bad Is Nothing Then bad.Interior.Color = vbRed   ' jede Fehlerformel auf einmal markieren

Das Muster zum Leerfüllen

Der klassische Grund, zu SpecialCells zu greifen, ist das Füllen von Lücken in einem Bericht — eine Beschriftung in jede leere Zelle darunter zu wiederholen oder leere Zahlen auf null zu setzen. Wählen Sie die leeren Zellen aus und schreiben Sie dann in einem Zug in die gesamte Auswahl. Um den Wert aus der Zelle darüber in jede leere Zelle zu kopieren, nutzen Sie eine relative R1C1-Formel und wandeln danach in Werte um:

Dim gaps As Range
On Error Resume Next
Set gaps = Range("A2:A5000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not gaps Is Nothing Then
    gaps.FormulaR1C1 = "=R[-1]C"      ' jede leere Zelle = die Zelle direkt darueber
    gaps.Value = gaps.Value          ' die Formeln in statische Werte einfrieren
End If

Das füllt Tausende Lücken in zwei Zeilen ohne Schleife. FormulaR1C1 = "=R[-1]C" bedeutet „eine Zeile nach oben, dieselbe Spalte“, in jede leere Zelle auf einmal geschrieben; die zweite Zeile ersetzt die Formeln durch ihre Ergebnisse, damit die Füllung ein Sortieren übersteht.

Das Muster zum Kopieren sichtbarer Zeilen (der Retter der Zeilenzahl)

xlCellTypeVisible ist der mit Abstand nützlichste Typ — wegen dem, was VBA ohne ihn tut. Nachdem Sie einen AutoFilter angewendet haben, sind die herausgefilterten Zeilen ausgeblendet, nicht weg — und ein schlichtes .Copy kopiert sie trotzdem. Um nur auf das zu wirken, was der Benutzer sieht, müssen Sie über SpecialCells(xlCellTypeVisible) gehen:

' Nur die Zeilen kopieren, die der Filter sichtbar gelassen hat - ausgeblendete Zeilen werden uebersprungen:
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("Summary").Range("A1")

Ohne xlCellTypeVisible kopiert das den ganzen Block samt ausgeblendeter Zeilen, und Sie fügen still Daten ein, die der Benutzer herausgefiltert hat. Das ist die Brücke zwischen dem Adressierungs-Cluster und echter Filterarbeit: CurrentRegion findet den Block, SpecialCells(xlCellTypeVisible) grenzt ihn auf die sichtbaren Zeilen ein. Dieselbe Idee löscht gefilterte Zeilen: filtern, dann .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete.

Die Mehrbereichs-Falle: das Ergebnis ist oft kein Rechteck

Das unterscheidet SpecialCells von jedem Bezug davor: der zurückgegebene Bereich ist meist nicht zusammenhängend. Zehn verstreute leere Zellen kommen als ein Bezug aus zehn getrennten Bereichen (Areas) zurück. Das ändert, wie Sie ihn inspizieren:

Dim vis As Range: Set vis = rng.SpecialCells(xlCellTypeVisible)
Debug.Print vis.Count            ' insgesamt sichtbare Zellen ueber ALLE Bereiche
Debug.Print vis.Areas.Count      ' wie viele getrennte Bloecke das sind
Dim a As Range
For Each a In vis.Areas
    Debug.Print a.Address        ' jeder zusammenhaengende Block, einer nach dem anderen
Next a

.Count ist die Gesamtzahl der Zellen; .Areas.Count ist, wie viele getrennte Blöcke sie bilden. Die meisten Operationen — ein Wert, eine Farbe, ein .Copy auf ein anderes Blatt — behandeln alle Bereiche auf einmal, Sie schleifen also selten. Die Ausnahme ist alles, was von der Reihenfolge abhängt: Wenn Sie mit SpecialCells gefundene Zeilen löschen, löschen Sie .EntireRow in einem Aufruf, statt nach oben zu schleifen, denn die Bereiche sind nicht garantiert von unten nach oben sortiert. Diese Mehrbereichs-Form teilt sich SpecialCells mit Union, das absichtlich denselben nicht zusammenhängenden Bezugstyp baut.

Wie ExcelMaster hilft

SpecialCells ist gerade deshalb mächtig, weil es scharf ist: Vergessen Sie den On Error-Schutz, bringt eine saubere Datei Ihr Makro zum Absturz; vergessen Sie xlCellTypeVisible, kopieren Sie die Zeilen, die ein Filter verbarg; behandeln Sie das Ergebnis als Rechteck, landet eine Schleife pro Zelle in der falschen Reihenfolge. Jeder dieser Fehler ist ein stiller, situationsabhängiger Fehlschlag — er läuft auf Ihren Daten und bricht auf denen eines anderen.

ExcelMaster lässt Sie stattdessen das Ergebnis beschreiben. Sagen Sie „fülle jede leere Zelle in Spalte A mit dem Wert darüber“ oder „kopiere nur die sichtbaren Zeilen auf ein Summary-Blatt“, und es schreibt den abgesicherten SpecialCells-Aufruf, die Is Nothing-Prüfung und die Nur-sichtbar-Weiterleitung für Sie — die Fassung, die eine Datei ohne leere Zellen und einen Filter übersteht, der die Hälfte der Zeilen verbirgt. Sie behalten die Arbeitsmappe und den Code; Sie sparen sich den Absturz beim ersten Grenzfall.

Häufig gestellte Fragen

Warum gibt SpecialCells den Fehler „Es wurden keine Zellen gefunden“ aus?

Weil SpecialCells den error 1004 wirft, wenn keine Zelle im durchsuchten Bereich zum gewünschten Typ passt — es behandelt „kein Treffer“ als Fehler, nicht als leeren Bereich. Ein Bereich ohne leere Zellen, durch SpecialCells(xlCellTypeBlanks) geschickt, hält das Makro an. Umschließen Sie den Aufruf mit On Error Resume Next, stellen Sie mit On Error GoTo 0 wieder her und prüfen Sie If Not result Is Nothing, bevor Sie ihn nutzen.

Wie wähle ich in VBA nach dem Filtern nur die sichtbaren Zellen aus?

Nehmen Sie xlCellTypeVisible: rng.SpecialCells(xlCellTypeVisible). Nach einem AutoFilter sind die herausgefilterten Zeilen ausgeblendet, aber weiterhin Teil des Bereichs, ein schlichtes .Copy schließt sie also ein. Der Weg über SpecialCells(xlCellTypeVisible) beschränkt das Kopieren, Einfärben oder Löschen auf die Zeilen, die der Benutzer tatsächlich sieht.

Was ist der Unterschied zwischen xlCellTypeConstants und xlCellTypeFormulas?

xlCellTypeConstants gibt Zellen zurück, die einen getippten Wert enthalten — Zahlen oder Text, die Sie direkt eingegeben haben. xlCellTypeFormulas gibt Zellen zurück, die eine Formel enthalten. Beide akzeptieren ein optionales zweites Argument, um nach Ergebnistyp einzugrenzen, sodass SpecialCells(xlCellTypeFormulas, xlErrors) nur die Formelzellen zurückgibt, die aktuell einen Fehler wie #REF! oder #DIV/0! anzeigen.

Wie fülle ich mit VBA alle leeren Zellen auf einmal?

Wählen Sie die leeren Zellen mit SpecialCells(xlCellTypeBlanks) (abgesichert gegen den Fehler ohne Treffer) und schreiben Sie dann in einer Anweisung in die gesamte Auswahl. Um den Wert über jeder leeren Zelle zu kopieren, nutzen Sie gaps.FormulaR1C1 = "=R[-1]C" und danach gaps.Value = gaps.Value, um die Formeln in statische Werte einzufrieren. Eine Schleife ist nicht nötig.

Gibt SpecialCells einen zusammenhängenden Bereich zurück?

Meist nicht. Die leeren oder sichtbaren Zellen, die Sie abfragen, sind oft verstreut, SpecialCells gibt also einen Bezug aus mehreren Bereichen zurück. .Count ist die Gesamtzahl der Zellen über alle Bereiche, und .Areas.Count ist, wie viele getrennte Blöcke sie bilden. Die meisten Operationen behandeln alle Bereiche auf einmal; schleifen Sie For Each a In result.Areas nur, wenn Sie jeden zusammenhängenden Block einzeln brauchen.

Getestet in

Getestet in: Excel 365 (Windows 11), VBA 7.1 — zuletzt geprüft am 16.08.2026.

Verwandte Anleitungen: VBA Union · VBA Intersect · VBA AutoFilter · VBA CurrentRegion · VBA On Error