Special Cells Methode

Die hier präsentierte VBA-Methode ist vielseitig einsetzbar, hilft bei entsprechend vielen Problemen und ist im Vergleich zu ihren Fähigkeiten erstaunlich wenig bekannt. Sie identifiziert Zellen mit speziellen Eigenschaften wie Formatierung, mit Kommentaren oder Konstanten, sichtbar, usw. Hier möchte ich ihre Syntax und einige der Einsatzmöglichkeiten vorstellen.

Zeilen mit Fehler ausblenden

Angenommen, in der Spalte F steht eine Formel, welche bestimmte Werte selektiert. Falls der Wert nicht existiert, wird dort ein Fehler generiert. Es gilt nun, alle Zeilen auszublenden, in denen dieser Fehler auftaucht. Dazu geht man zweistufig vor:

1.     Z.B. in die Spalte A schreiben wir in jede Zelle des Bereiches die Formel
=WENN(ISTFEHLER(F Zeilennummer);“Hide“;0)
Auf diese Art sorgen wir dafür, dass in der Zelle ein Text steht, wenn es einen Fehler gibt, und eine Zahl, wenn kein Fehler vorliegt.

2.     Danach lassen wir den Nutzer auf eine Schaltfläche klicken, hinter der folgender VBA Code steht (der zu betrachtende Bereich geht von Zeile 15 bis Zeile 150):

Sub FehlerZeilenAusblenden()

Dim rngArea as Range

ActiveSheet.Range(“A15:A150“).Select

Selection.SpecialCells(xlCellTypeFormulas, 2).Select ‘die “2” bedeutet, dass die Zelle markiert wird, wenn in ihr ein Text steht. Wollten wir die Zellen mit Zahlen markieren, müssten wir hier den Wert „1“ nehmen.
For each rngArea in Selection

            rngArea.EntireRow.Hidden = True

Next

End Sub

Die SpecialCells-Value Bedingung definiert die Zelltypen, welche in das Ergebnis eingeschlossen werden sollen. Eine Bedingung ist xlCellTypeFormulas, die dafür sorgt, dass nur Zellen eingeschlossen werden, in denen eine Formel steht. Die Zahl hinter der xlCellTypesFormulas Methode spezifiziert das Formelergebnis und kann folgende Werte annehmen:

 

Wert	Bedeutung	VBA Schreibweise
1	Zahlen	xlNumbers
2	Texte	xlTextValues
4	Logische Werte	xlLogical
16	Fehler	xlErrors

Weil die Spezifizierung entweder als Zahl oder VBA-Anweisung angegeben werden kann, habe ich beides in der obigen Tabelle berücksichtigt.

Anders ausgedrückt: hier werden Zellen analysiert, in denen Formeln stehen und deren Ergebnis eine Zahl, ein Text usw. sein kann. Dies Ergebnis wird analysiert und wenn es dem angegebenen Wert entspricht, wird die betreffende Zelle einer Selektion oder einem Range zugeordnet. Die Rückgabe der SpecialCells Methode ist also ein Range Objekt, welches nur Zellen mit bestimmten Inhalten enthält.

Die Konstanten, die an die SpecialCells Methode übergeben werden können sind:

xlCellType-Konstante	Bedeutung
xlCellTypeAllFormatConditions	Zellen mit beliebiger Formatierung
xlCellTypeAllValidation	Zellen mit Gültigkeitskriterien
xlCellTypeBlanks	Leerzellen
xlCellTypeComments	Zellen mit Kommentaren
xlCellTypeConstants	Zellen mit Konstanten
xlCellTypeFormulas	Zellen mit Formeln
xlCellTypeLastCell	Die letzte Zelle im verwendeten Bereich
xlCellTypeSameFormatConditions	Zellen mit gleichem Format
xlCellTypeSameValidation	Zellen mit gleichen Gültigkeitskriterien
xlCellTypeVisible	Alle sichtbaren Zellen

 

Leere Zeilen ausblenden

Wenn wir die gesamten leeren Zeilen eines Arbeitsblattes ausblenden wollen, hilft dieses Makro:

Sub HideEmpty()

Dim lngLastRow as Long

Dim i as Integer

           

            lngLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
            ‘Über den SpecialCells-Befehl wird die letzte benutze Zelle aktiviert. Mit der Eigenschaft „Row“ wird die Zeile markiert, mit „Column“ würde man die entsprechende Spalte markieren.

            Application.ScreenUpdating = False ‘Damit wird verhindert, dass der Bildschirm nach jeder Aktion neu geschrieben wird und somit bei einer derartigen Aktion flimmert.

For i = 1 to lngLastRow

            If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then ‘CountA prüft, ob die Zelle einen Wert enthält. Wenn nicht, wird die Then-Aktion umgesetzt.

                       Rows(i).Hidden = True
            End If
            Next i
            Application.ScreenUpdating = True

End Sub

Wollen wir nur die Zeilen ausblenden, bei denen in der Spalte A nichts drin steht, dann reicht diese Anweisung:

Range(“A3:A75“).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden=True

Diese Zeilen löschen wir mit

Range(“A3:A75“).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

Alle Zahlen in Währung umformatieren

Es ist leicht, mit Strg+H existierende Werte durch andere zu ersetzen. Das funktioniert allerdings nicht mit Formaten. Eine einzige Zeile VBA-Code kann aber die Aufgabe, alle Zahlwerte in Währungswerte umzuformatieren, erfüllen:

Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Style =”Currency”

Durch das Range-Objekt “Cells” wird Excel veranlasst, alle Zellen zu durchsuchen und alle mit konstanten Zahlwerten umzuformatieren.

Textzellen einfärben

Ähnlich können wir vorgehen, wenn wir alle Textzellen einfärben möchten:

Cells.SpecialCells(xlCellTypeConstants, xlTextValues (oder einfach “2”)) .FontColorIndex=3

Kombination von Methoden

Wenn wir nun etwas mit Zellen anstellen möchten, die mehr als ein Kriterium erfüllen müssen, können wir die SpecialCells Methoden kombinieren. Wollen wir beispielsweise alle Zellen mit Zahlen und Datenvalidierung rot einfärben, machen wir das mit dieser Code-Zeile:

Cells.SpecialCells(xlCellTypeConstants, 1).SpecialCells(xlCellTypeAllValidation).Font.Color = vbRed

Verwendung in If-Then-Else Entscheidungen

In diesem Beispiel sollen allen Zellen mit einem Wert größer als 7500 getestet werden. Wenn sie größer sind, sollen sie um 10% erhöht werden.

Sub Test()

Dim RG as Range

 

For each RG in Cells.SpecialCells(xlCellTypeFormulas, 1)

            If RG.Value > 7500 Then

                       RG.Value = RG.Value * 1.1

            End If

Next RG

 

End Sub