VBA-Lösungen für Zeilen, Zellen und Bereiche

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.