VBA: Bereich nach Spalten mit Kriterien filtern

[boott]

Guten Tag

Ich habe in Excel folgenden Bereich:

Range(Cells(firstRow, firstColumn), Cells(lastRow, lastColumn))

Nun sollen aus diesem Bereich nur jene Spalten ausgewählt werden, welche in Zeile 20 nicht leer sind.

Hat jemand eine Idee, wie das in VBA zu bewerkstelligen ist?

Gruss
boott

 

[weer]

Nun sollen aus diesem Bereich nur jene Spalten ausgewählt werden, welche in Zeile 20 nicht leer sind.

Hallo boott
Ich weiss nicht genau, was Du mit ausgewählt meinst. In meinem Makro werden die Spalten-Bereiche ausgewählt, in denen die Zeile 20 nicht leer ist. Ich habe mich dabei inspirieren lassen von einem Beitrag in ms-office-forum.net vor zwei Jahren.

Im Makro musst Du anpassen bzw. ändern:
fr = Die erste Zeile Deines Bereiches
fc = Die erste Spalte Deines Bereiches
lr = Die letzte Zeile Deines Bereiches
lc = Die letzte Spalte Deines Bereiches
leerz = die Zeile, die nicht leer sein darf

In meinem Makro habe ich folgende Variablen definiert
fr = 1: fc = 1
lr = 25: lc = 7
Mein Bereich umfasst also A1:G25

Ich hoffe, das Makro hilft Dir ein Stück weiter.
Grüsse Niclaus

Sub test()
Dim fr, fc, lr, lc, leerz, i
Dim rngBereich As Range

fr = 1: fc = 1
lr = 25: lc = 7
leerz = 20

For i = 1 To lc
If Not IsEmpty(Cells(leerz, i)) Then
    If rngBereich Is Nothing Then
        Set rngBereich = Range(Cells(fr, i), Cells(lr, i))
    Else
        Set rngBereich = Union(rngBereich, Range(Cells(fr, i), Cells(lr, i)))
    End If
End If
Next i

rngBereich.Select
Set rngBereich = Nothing
End Sub

 

[boott]

Hallo Niclaus

Vielen Dank. So funktionierts.
Ich musste jedoch den Bereich mit «Nothing» initialisieren.

Set rngBereich = Nothing

Nun hab ich mehrere solche Bereiche erstellt.
Wenn ich nun die Anzahl Zellen aller dieser Bereiche zählen will, kann ich das mit

cellCount = Bereich1.Cells.Count + Bereich2.Cells.Count + ...

tun – vorausgesetzt, keiner dieser Bereiche ist leer (= Nothing).
Leider ist diese Voraussetzung nicht immer erfüllt. Kennst du einen Trick, wie das mit teilweise leeren Bereichen geht, ohne jeden einzelnen Bereich auf emptyness prüfen zu müssen?

Gruss
boott

 

[weer]

Hallo Niclaus
Nun hab ich mehrere solche Bereiche erstellt.
Wenn ich nun die Anzahl Zellen aller dieser Bereiche zählen will, kann ich das mit
cellCount = Bereich1.Cells.Count + Bereich2.Cells.Count + ...
tun – vorausgesetzt, keiner dieser Bereiche ist leer (= Nothing).
Leider ist diese Voraussetzung nicht immer erfüllt. Kennst du einen Trick, wie das mit teilweise leeren Bereichen geht, ohne jeden einzelnen Bereich auf emptyness prüfen zu müssen?

Hallo boott

Ich weiss nicht, wie Du zu "Bereich1", "Bereich2" usw. kommst. Kannst Du mal Dein ganzes Makro zeigen?

Ich verstehe auch nicht, was Du gezählt haben willst: Die Anzahl Zellen im gefilterten Bereich? Die Anzahl nicht-leerer Zellen im gefilterten Bereich? Die Anzahl Zahlen im gefilterten Bereich?

Wenn Du etwas davon wissen willst, dann ändere doch mal mein Makro folgendermassen:

Sub test2()
Dim fr, fc, lr, lc, leerz, i
Dim rngBereich As Range

fr = 1: fc = 1
lr = 25: lc = 7
leerz = 20

For i = 1 To lc
If Not IsEmpty(Cells(leerz, i)) Then
    If rngBereich Is Nothing Then
        Set rngBereich = Range(Cells(fr, i), Cells(lr, i))
    Else
        Set rngBereich = Union(rngBereich, Range(Cells(fr, i), Cells(lr, i)))
    End If
End If
Next i

rngBereich.Select

xx = Selection.Count
yy = WorksheetFunction.CountA(Selection)
zz = WorksheetFunction.Count(Selection)

MsgBox "Der gefilterte Bereich umfasst insgesamt " & xx & " Zellen."
MsgBox "Der gefilterte Bereich enthält " & yy & " nicht leere Zellen."
MsgBox "Der gefilterte Bereich enthält " & zz & " Zahlenwerte."

Set rngBereich = Nothing
End Sub

Grüsse Niclaus