In diesem Beitrag sind kleinere Aufgaben rund um Zeilen, Zellen und Bereiche gesammelt, die in der Praxis immer wieder vorkommen. Jede Lösung ist für sich verwendbar. Den Code fügen Sie über Alt+F11 > Einfügen > Modul ein.
Leere Zeilen löschen
Nach einem Import stehen oft leere Zeilen zwischen den Daten. Die Funktion löscht alle Zeilen, die vollständig leer sind:
Option Explicit
' Löscht alle Zeilen im Datenbereich eines Blatts, die vollständig leer sind.
Public Function LeerzeilenLoeschen(ByVal ws As Worksheet) As Long
Dim zeile As Long, letzte As Long
letzte = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
For zeile = letzte To 1 Step -1 ' von unten, sonst werden Zeilen übersprungen
If Application.WorksheetFunction.CountA(ws.Rows(zeile)) = 0 Then
ws.Rows(zeile).Delete
LeerzeilenLoeschen = LeerzeilenLoeschen + 1
End If
Next zeile
End Function
Aufruf: LeerzeilenLoeschen Worksheets("Import"). Die Funktion gibt die Anzahl gelöschter Zeilen zurück. Anders als ältere Fassungen, die nur Spalte A prüfen, bleibt eine Zeile erhalten, wenn irgendwo in ihr etwas steht.
Für Zeilen, die nach einem Kennzeichen gelöscht werden sollen, gibt es einen eigenen Beitrag: Zeilen per VBA abhängig von einer Bedingung löschen.
Schneller bei großen Listen
Bei einigen Tausend Zeilen wird die Funktion oben spürbar langsam, weil Excel nach jedem einzelnen Löschen die Zeilen darunter verschiebt. Schneller ist es, die leeren Zeilen zuerst zu sammeln und dann in einem einzigen Schritt zu löschen:
Option Explicit
' Löscht alle vollständig leeren Zeilen in einem Schritt. Gibt die Anzahl zurück.
Public Function LeerzeilenLoeschenSchnell(ByVal ws As Worksheet) As Long
Dim zeile As Long, letzte As Long
Dim fund As Range, weg As Range
Set fund = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If fund Is Nothing Then Exit Function ' leeres Blatt
letzte = fund.Row
For zeile = 1 To letzte
If Application.WorksheetFunction.CountA(ws.Rows(zeile)) = 0 Then
If weg Is Nothing Then
Set weg = ws.Rows(zeile)
Else
Set weg = Union(weg, ws.Rows(zeile))
End If
LeerzeilenLoeschenSchnell = LeerzeilenLoeschenSchnell + 1
End If
Next zeile
If Not weg Is Nothing Then weg.Delete ' ein einziger Löschvorgang
End Function
Union fügt die leeren Zeilen zu einem Bereich zusammen, weg.Delete entfernt sie auf einmal. Die letzte Zeile ermittelt Find, damit nur formatierte Zeilen unterhalb der Daten nicht mitgezählt werden. Mehr zu den Unterschieden der Methoden im Beitrag Letzte Zeile und letzte Spalte per VBA ermitteln.
Ohne VBA: Liste ohne Leerzeilen per Formel (Microsoft 365)
Soll die Quelle unverändert bleiben, etwa bei einem Import, der jeden Monat neu kommt, erzeugt eine Formel eine bereinigte Kopie:
=FILTER(A1:D500;NACHZEILE(A1:D500;LAMBDA(z;ANZAHL2(z)>0)))
NACHZEILE prüft jede Zeile mit ANZAHL2, ob überhaupt etwas darin steht, FILTER behält nur diese Zeilen. Einzelne leere Zellen in sonst gefüllten Zeilen erscheinen im Ergebnis als 0. Soll dort nichts stehen, hängen Sie an den Bereich &"" an, dann werden allerdings auch Zahlen zu Text.
Text in einem ganzen Bereich bereinigen
Importierte Daten enthalten oft Leerzeichen am Anfang oder Ende, doppelte Leerzeichen oder geschützte Leerzeichen aus Webseiten. Für Vergleiche und Verweise ist „Müller GmbH “ dann ein anderer Text als „Müller GmbH“. Die Funktion bereinigt alle Textzellen eines Bereichs und arbeitet dabei mit einem Datenfeld, also in einem einzigen Lese- und Schreibzugriff. Das ist auch bei Zehntausenden Zellen schnell:
Option Explicit
' Entfernt Leerzeichen am Rand, doppelte Leerzeichen und geschützte Leerzeichen
' in allen Textzellen eines Bereichs. Gibt die Anzahl geänderter Zellen zurück.
Public Function BereichGlaetten(ByVal bereich As Range) As Long
Dim werte As Variant
Dim i As Long, j As Long
Dim neu As String
Select Case bereich.HasFormula
Case False ' nur Werte: weiter
Case Else ' True oder gemischt (Null)
Err.Raise vbObjectError + 513, "BereichGlaetten", "Der Bereich enthält Formeln."
End Select
If bereich.CountLarge = 1 Then
ReDim werte(1 To 1, 1 To 1)
werte(1, 1) = bereich.Value2
Else
werte = bereich.Value2 ' ganzer Bereich in einem Zugriff
End If
For i = 1 To UBound(werte, 1)
For j = 1 To UBound(werte, 2)
If VarType(werte(i, j)) = vbString Then
neu = Replace(werte(i, j), Chr$(160), " ")
neu = Application.WorksheetFunction.Trim(neu) ' wie GLÄTTEN
If neu <> werte(i, j) Then
werte(i, j) = neu
BereichGlaetten = BereichGlaetten + 1
End If
End If
Next j
Next i
bereich.Value2 = werte ' zurückschreiben in einem Zugriff
End Function
Aufruf: MsgBox BereichGlaetten(Worksheets("Import").Range("A1:F5000")) & " Zellen bereinigt". Zahlen und leere Zellen bleiben unverändert. Enthält der Bereich Formeln, bricht die Funktion mit einer Meldung ab, denn beim Zurückschreiben der Werte gingen die Formeln verloren. Der Unterschied zu einer Schleife über die einzelnen Zellen ist erheblich: Jeder Zugriff auf eine Zelle kostet Zeit, das Datenfeld dagegen liegt im Arbeitsspeicher.
Einträge innerhalb einer Zelle sortieren
Steht in einer Zelle „Süd, Nord, Ost, West“, sortiert diese Funktion die Einträge:
Option Explicit
' Sortiert die Einträge innerhalb eines Texts, etwa "Nord, Ost, Süd" aus "Süd, Nord, Ost".
' Verwendbar in einer Zelle: =InZelleSortieren(A1;", ")
Public Function InZelleSortieren(ByVal text As String, Optional ByVal trenner As String = ",") As String
Dim teile() As String, i As Long, j As Long, tausch As String
teile = Split(text, trenner)
For i = LBound(teile) To UBound(teile)
teile(i) = Trim$(teile(i))
Next i
For i = LBound(teile) To UBound(teile) - 1 ' einfaches Sortieren, für kurze Listen ausreichend
For j = i + 1 To UBound(teile)
If StrComp(teile(j), teile(i), vbTextCompare) < 0 Then
tausch = teile(i): teile(i) = teile(j): teile(j) = tausch
End If
Next j
Next i
InZelleSortieren = Join(teile, trenner & IIf(Right$(trenner, 1) = " ", "", " "))
End Function
In einer Zelle: =InZelleSortieren(A1;", ") ergibt „Nord, Ost, Süd, West“. Groß- und Kleinschreibung spielen beim Sortieren keine Rolle.
In Microsoft 365 geht es auch ohne VBA:
=TEXTVERKETTEN(", ";WAHR;SORTIEREN(TEXTTEILEN(A1;;", ")))
Markierte Zeilen zählen
Selection.Rows.Count zählt nur den ersten Teil einer Markierung. Wer mit Strg mehrere Bereiche markiert, bekommt ein falsches Ergebnis. Für A1:A3 und C5:C9 liefert es die Zahl 3 statt 8. Die Funktion zählt alle Teile:
Option Explicit
' Zählt die Zeilen einer Markierung, auch wenn sie aus mehreren Teilen besteht (Strg+Klick).
Public Function MarkierteZeilen(ByVal auswahl As Range) As Long
Dim teil As Range
For Each teil In auswahl.Areas
MarkierteZeilen = MarkierteZeilen + teil.Rows.Count
Next teil
End Function
Aufruf: MsgBox MarkierteZeilen(Selection) & " Zeilen markiert". Ohne Makro zeigt übrigens die Statusleiste unten rechts die Anzahl, wenn Sie mit der rechten Maustaste darauf klicken und „Anzahl“ einschalten.
Nach Schriftfarbe summieren
Formeln können Formatierungen nicht auswerten. Diese Funktion summiert alle Zahlen, deren Schriftfarbe der einer Musterzelle entspricht:
Option Explicit
' Summiert alle Zahlen eines Bereichs mit derselben Schriftfarbe wie die Musterzelle.
' Verwendbar in einer Zelle: =SummeNachSchriftfarbe(B2:B20;D1)
Public Function SummeNachSchriftfarbe(ByVal bereich As Range, ByVal muster As Range) As Double
Dim zelle As Range
Application.Volatile ' bei jeder Neuberechnung aktualisieren
For Each zelle In bereich.Cells
If zelle.Font.Color = muster.Font.Color And IsNumeric(zelle.Value) And Not IsEmpty(zelle.Value) Then
SummeNachSchriftfarbe = SummeNachSchriftfarbe + zelle.Value
End If
Next zelle
End Function
In einer Zelle: =SummeNachSchriftfarbe(B2:B20;D1), wobei D1 eine Zelle in der gesuchten Farbe ist.
Wichtig: Eine reine Farbänderung löst in Excel keine Neuberechnung aus. Färben Sie eine Zelle um, bleibt die Summe zum Beispiel bei 60, erst nach F9 zeigt sie die richtigen 90. Wer viel mit Farbe als Information arbeitet, fährt mit einer zusätzlichen Spalte für den Status besser: Danach kann jede normale Formel summieren und filtern.
Ohne VBA: Für eine einmalige Auswertung reicht oft der Filter nach Farbe. Schreiben Sie unter die Liste =TEILERGEBNIS(109;B2:B20) und filtern Sie über das Filtersymbol der Spalte, Nach Farbe filtern, die gewünschte Schriftfarbe. TEILERGEBNIS mit 109 summiert nur die sichtbaren Zeilen. Sind von 10, 20 und 30 die 20 ausgeblendet, ergibt die Formel 40.
Namen im Namensmanager ausblenden
Namen für Konstanten wie den Umsatzsteuersatz sollen nicht versehentlich geändert werden. Ausgeblendete Namen erscheinen nicht im Namensmanager, Formeln rechnen aber weiter damit:
Option Explicit
' Blendet alle Namen der Mappe im Namensmanager aus (False) oder wieder ein (True).
Public Sub NamenSichtbar(ByVal sichtbar As Boolean)
Dim n As Name
For Each n In ThisWorkbook.Names
If Not n.Name Like "*_FilterDatabase" And Not n.Name Like "*Print_*" Then n.Visible = sichtbar
Next n
End Sub
NamenSichtbar False blendet aus, NamenSichtbar True wieder ein. Namen, die Excel selbst für Filter und Druckbereiche anlegt, lässt der Code unberührt. Das ist kein Schutz: Jeder mit VBA-Kenntnissen kann die Namen wieder einblenden. Es verhindert aber versehentliche Änderungen.
Bilder aus einem Ordner einfügen
Für eine Produktliste mit Fotos fügt der Code alle Bilder eines Ordners untereinander ein, passt sie an die Spaltenbreite an und schreibt den Dateinamen daneben:
Option Explicit
' Fügt alle Bilder eines Ordners untereinander in Spalte A ein, skaliert auf die Spaltenbreite,
' und schreibt den Dateinamen in Spalte B. Die Bilder werden in die Datei eingebettet.
Public Function BilderEinfuegen(ByVal ordner As String, ByVal ws As Worksheet, Optional ByVal startzeile As Long = 2) As Long
Dim datei As String, zeile As Long, bild As Shape, breite As Double
If Right$(ordner, 1) <> "\" Then ordner = ordner & "\"
breite = ws.Columns("A").Width
zeile = startzeile
datei = Dir$(ordner & "*.*")
Do While Len(datei) > 0
Select Case LCase$(Mid$(datei, InStrRev(datei, ".") + 1))
Case "jpg", "jpeg", "png", "gif", "bmp"
Set bild = ws.Shapes.AddPicture(ordner & datei, LinkToFile:=False, SaveWithDocument:=True, _
Left:=ws.Cells(zeile, 1).Left, Top:=ws.Cells(zeile, 1).Top, Width:=-1, Height:=-1)
bild.LockAspectRatio = msoTrue
bild.Width = breite ' auf Spaltenbreite skalieren, Höhe folgt
ws.Rows(zeile).RowHeight = bild.Height
ws.Cells(zeile, 2).Value = datei
zeile = zeile + 1
BilderEinfuegen = BilderEinfuegen + 1
End Select
datei = Dir$
Loop
End Function
Aufruf: BilderEinfuegen "C:\Fotos\Artikel", Worksheets("Liste").
Ältere Fassungen verwenden Pictures.Insert. Je nach Excel-Version fügt das Bilder nur als Verknüpfung ein, und beim Empfänger der Datei fehlen sie. Shapes.AddPicture mit SaveWithDocument:=True bettet sie sicher ein. In Microsoft 365 gibt es außerdem „Bild in Zelle“, beschrieben im Beitrag Bild abhängig von einer Auswahl in Excel anzeigen.
Mit KI schneller zum Ziel
Kleine Makros wie diese gehören zu den Aufgaben, bei denen ChatGPT, Claude oder Copilot am meisten Zeit sparen. Ein Punkt fällt bei generiertem Code aber immer wieder auf: Er arbeitet Zelle für Zelle mit Select und Activate oder löscht Zeilen einzeln. Bei zwanzig Testzeilen merkt man davon nichts, bei zwanzigtausend Zeilen im echten Import läuft das Makro Minuten statt Sekunden. Bitten Sie die KI deshalb ausdrücklich um Code „ohne Select, mit Datenfeld und einem einzigen Löschvorgang“, und testen Sie mit einer realistisch großen Datenmenge.
Wie Entwickler mit KI-Agenten zuverlässig VBA schreiben, beschreibt der Beitrag KI-Coding-Agenten für VBA: Claude Code und Codex. Sind KI-erzeugte Makros bei Ihnen schon im Einsatz, prüfen wir sie im Rahmen einer Prüfung von Arbeitsmappen auf Fehler und Rechenzeit. Wie wir KI selbst nutzen, um Entwicklungszeit und Kosten zu senken, zeigt die Seite KI und Automatisierung.
Aus der Praxis: wenn das Makro zu lange läuft
Viele Makros wachsen über Jahre: Erst verarbeiten sie hundert Zeilen, dann zehntausend, und irgendwann dauert der Monatslauf eine halbe Stunde. Meist liegt es an denselben Mustern, also einzelnen Zellzugriffen, Select, Neuberechnung und Bildschirmaktualisierung während des Laufs. Solche Makros analysieren und beschleunigen wir, ohne dass sich für die Anwender etwas an der Bedienung ändert. Mehr dazu unter Excel-Programmierung.
Passende Beiträge
Lieber machen lassen?
Wir programmieren die Lösung für Ihre Datei.
Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.
