CurrentRegion - Die wirkliche Kontrolle

Was ist überhaupt CurrentRegion?
Nun, nichts anderes als der gesammte zusammenhängende Bereich - letztendlich eine intakte Liste.
Siehe: Listen (Tabellen)
Sie sollten unbedingt mit dem Begriff Listen vertraut sein

Erstellen Sie eine neue Arbeitsmappe mit mindestens dem Tabellenblatt Tabelle1

Tabelle1

	A	B	C	D
1
2		Äpfel	Birnen
3		4	10
4		3	11
5		2	12
6

und in einem Allgemeinen Modul die Prozedur

Public Sub CurReg()
MsgBox ThisWorkbook.Worksheets("Tabelle1").Range("b2").CurrentRegion.Address
End Sub



Wenn Sie diese Prozedur nun ausführen, gibt, welch Überraschung, die MsgBox den Bereich aus. Und zwar den Zusammenhängenden.

Schreiben Sie nun in die Zelle D4 irgendetwas hinein und überlegen Sie sich selbst, was die MsgBox zurückgibt, bevor Sie die Prozedur ausführen.

Machen Sie noch ein paar weitere Experimente,z.B. leeren Sie wieder D4 und leeren Sie auch die Zellen B4 und C4. Sie sehen:
CurrentRegion ist nur bei intakten Listen sinnvoll!

Ein kleines - aber hoffentlich überzeugendes - Beispiel

Stellen Sie nun den Ursprungszustand wieder her.

Nun setzen Sie einen Autofilter auf den Bereich an und filtern Sie Äpfel nach 3.
Führen Sie nun wieder unsere Prozedur aus und tatsächlich, wir erhalten immer noch unseren gesamten Bereich!

So lapidar dies erscheinen mag, so wichtig ist dies doch!
Viele andere Ermittlungen in Excel VBA haben mit gefilterten Listen, ausgeblendeten Zeilen etc... größte Probleme und sind schnell dadurch Fehleranfällig.

Heben Sie den Filter wieder auf.
Nehmen wir an, wir wollten die nächste freie Zeile ermitteln, basierend auf einem Eintrag in Äpfel.
Beliebt für die erste freie Zelle von unten - und bei allem eingeblendet auch ok: (Hier ohne weitere Prüfungen)

Public Sub LetzteZelleNurSo()
With ThisWorkbook.Worksheets("Tabelle1")
   MsgBox .Cells(.Rows.Count, 2).End(xlUp).Address
End With
End Sub


Dies gibt uns die letzte beschriebene Zelle zurück und wir müssen nur 1 erhöhen.
Filtern Sie nun wieder wie oben beschrieben und dann nocheinmal.

Jetzt haben wir den Salat!
Diese Methode beschränkt sich nämlich auf die sichtbaren Zellen - und dies kann sehr schnell unangenehm werden!

Techniken mit CurrentRegion

Tatsächlich sind diese sehr einfach und benötigen z.T. nur etwas Mathe und Standardtechniken.

Letzte Zeile Wir wollen die letzte beschriebene Zeile unseres Bereiches numerisch erhalten:

Public Sub LezteZeileImBereich()
With ThisWorkbook.Worksheets("Tabelle1").Range("b2").CurrentRegion
   MsgBox .Row + .Rows.Count - 1
End With
End Sub

Die Row Eigenschaft gibt uns die Startzeile unseres CurrentRegion.
Die Rows Eigenschaft gibt uns die Anzahl der Zeilen unseres CurrentRegion
Diese einfach zusammengezählt und noch 1 abgezogen und schwupps haben wir unsere letzte Zeile

Und wie erhalten wir die nächste freie Zeile???
YEP, die Frage war jetzt besonders schwer, wir lassen einfach die -1 weg.

Und das Ganze - wie Sie testen können - unabhängig von sichtbaren Zellen.
(Analog funktioniert dies natürlich auch für Spalten, ist aber selten sinnvoll zu gebrauchen)

All diese Berechnungen sind dann gerade mit dem Cells Objekt ideal weiterzuverarbeiten.

Oder mit Offset
Public Sub OderMitOffset()
With ThisWorkbook.Worksheets("Tabelle1").Range("b2").CurrentRegion
   MsgBox .Offset(.Rows.Count, 0).Row - 1
End With
End Sub



Haben Sie sich einmal damit vertraut gemacht, ist dies ganze denkbar simpel.

Zeilen ausblenden nutzen

Um dies zu verstehen, zuerst ein neuer Aufbau:
Tabelle1

	A	B	C	D
1
2		Äpfel	Birnen
3		4	10
4		3	11
5		2	12
6
7		a	b
8

Führen Sie nun unsere Einstiegsprozedur CurReg aus.
Wie wir nun wissen, gibt diese korrekt B2 bis C5 aus, da die Liste ja unterbrochen ist.
Blenden Sie nun die Zeile 6 aus (also die leere Zeile) und führen Sie wieder die CurReg aus.
Auch hier wird wieder B2 bis C5 ausgegeben.

Dies ist wichtig zu verstehen! Der CurrentRegion ist und bleibt der zusammenhängende Bereich und ist unabhängig von ausgeblendeten Zeilen/Spalten.

Hat man dies einmal verstanden, kann man sich dies auch bei Berechnungen und Einfügen von Zeilen zu Nutze machen.
Nun das Beispiel dazu, welches dem reinen Gedanken von Listen widerspricht aber doch oft genutzt wird, nämlich unter Listen Berechnungen durchzuführen, z.B. Summen.
Stellen Sie nun folgendes her:
Tabelle1

	A	B	C	D
1
2		Äpfel	Birnen
3		4	10
4		3	11
5		2	12
6
7		9	33

Formeln der Tabelle

Zelle Formel B7 =SUMME(B3:B6) C7 =SUMME(C3:C6)

Beachten Sie unbedingt die korrekte Adressierung in den Summenformeln.
Als nächstes blenden wir die Zeile 6 aus, also sieht es so aus:

Tabelle1

	A	B	C	D
1
2		Äpfel	Birnen
3		4	10
4		3	11
5		2	12
7		9	33

Formeln der Tabelle

Zelle Formel B7 =SUMME(B3:B6) C7 =SUMME(C3:C6)

Unser Ziel ist es selbstverständlich, unsere Liste mit Werten zu erweitern und zwar per Code. Woher unsere Werte auch immer kommen mögen, im Beispiel nehme ich einfach mal 2 feste Werte.

Public Sub ListeErweitern()
Dim iZeile As Long
With ThisWorkbook.Worksheets("Tabelle1").Range("b2").CurrentRegion
   iZeile = .Row + .Rows.Count ' Die Leere Zeile - Ausgeblendet
End With
With ThisWorkbook.Worksheets("Tabelle1")
   .Rows(iZeile).Insert Shift:=xlDown
   .Cells(iZeile, 2) = 5 ' Wert für Äpfel
   .Cells(iZeile, 3) = 10 ' Wert für Birnen
   ' Selbstverständlich könnten wir uns die Spaltenpos
   ' auch aus unserem CurrentRegion berechnen
End With
End Sub

Hier nutzen wir alles, was Excel zu bieten hat.
Da unsere Summenfunktion die ausgeblendete Zeile mit einschließt, funktioniert dies.
Wir drücken per Code den Bereich auf und zwar überhalb unserer ausgeblendeten Zeile.
Damit erreichen wir, dass die Summenformel von Excel automatisch angepasst wird.

Zum Abschluss:

CurrentRegion eröffnet viele Möglichkeiten.
Experimentieren Sie damit, dann wird er bald ein guter Freund.