VBA – Dynamischer Bereich

Dieser Artikel zeigt Ihnen, wie Sie einen dynamischen Bereich in Excel VBA erstellen können.

Wenn Sie in Excel-VBA einen bestimmten Zellenbereich als Variable deklarieren, können Sie nur mit diesen bestimmten Zellen arbeiten. Durch das Deklarieren von dynamischen Bereichen in Excel erhalten wir weitaus mehr Flexibilität in Bezug auf unseren Code und die Funktionen, die er ausführen kann.

Auf Bereiche und Zellen verweisen

Wenn wir in Excel auf das Bereichs- oder Zellenobjekt verweisen, geben wir normalerweise die gewünschten Zeilen und Spalten fest ein.

Die Range-Eigenschaft

Mithilfe der Range-Eigenschaft können wir in den folgenden Beispielcodezeilen Aktionen wie z. B. die Änderung der Zellenfarbe oder die Darstellung der Zellen in fetter Schrift für diesen Bereich durchführen.

Range("A1:A5").Font.Color = vbRed
Range("A1:A5").Font.Bold = True

Die Cells-Eigenschaft

In ähnlicher Weise können wir die Cells-Eigenschaft verwenden, um auf einen Zellenbereich zu verweisen, indem wir auf die Zeile und Spalte in der Cells-Eigenschaft direkt verweisen. Die Zeile muss immer eine Zahl sein, aber die Spalte kann eine Zahl oder ein in Anführungszeichen eingeschlossener Buchstabe sein.

Auf die Zellenadresse A1 kann zum Beispiel wie folgt verwiesen werden:

Cells(1,1)

Oder

Cells(1, "A")

Um die Cells-Eigenschaft zum Verweisen auf einen Zellenbereich zu verwenden, müssen wir den Anfang und das Ende des Bereichs angeben.

Um zum Beispiel auf den Bereich A1: A6 zu verweisen, könnten wir die folgende Syntax verwenden:

Range(Cells(1,1), Cells(1,6))

Anschließend können wir die Cells-Eigenschaft verwenden, um Aktionen mit dem Bereich wie in den folgenden Beispielcodezeilen beschrieben durchzuführen:

Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRed
Range(Cells(2, 2), Cells(6, 2)).Font.Bold = True

Dynamische Bereiche mit Variablen

Da sich die Größe unserer Daten in Excel ändert (d. h. wir verwenden mehr Zeilen und Spalten als in den Bereichen, die wir im Code angegeben haben), wäre es nützlich, wenn sich die Bereiche, auf die wir in unserem Code verweisen, ebenfalls ändern würden. Mit dem obigen Range-Objekt können wir Variablen erstellen, um die maximalen Zeilen- und Spaltennummern des von uns verwendeten Bereichs des Excel-Arbeitsblatts zu speichern und diese Variablen verwenden, um das Range-Objekt dynamisch anzupassen, während der Code ausgeführt wird.

Zum Beispiel

Dim lZeile as Integer
Dim lSpalte as Integer
lZeile = Range("A1048576").End(xlUp).Row
lSpalte = Range("XFD1").End(xlToLeft).Column

Letzte Zeile in der Spalte

Da ein Arbeitsblatt 1048576 Zeilen hat, geht die Variable lZeile bis zum unteren Rand des Blattes und verwendet dann die spezielle Kombination aus den Tasten Ende und der Pfeil nach oben, um zur letzten im Arbeitsblatt verwendeten Zeile zu springen. Dies gibt uns die Zeilennummer, die wir in unserem Bereich benötigen.

Letzte Spalte in der Zeile

In ähnlicher Weise bewegt sich lSpalte zur Spalte XFD, die die letzte Spalte im Arbeitsblatt ist und verwendet dann die spezielle Kombination aus den Tasten Ende und der Pfeil nach links, um zur letzten im Arbeitsblatt verwendeten Spalte zu springen. Dies gibt uns die Spaltennummer, die wir in unserem Bereich benötigen.

Um den gesamten im Arbeitsblatt verwendeten Bereich zu erhalten, können wir also den folgenden Code ausführen:

Sub BereichErmitteln()
  Dim lZeile As Integer
  Dim lSpalte As Integer
  Dim bereich As Range
  lZeile = Range("A1048576").End(xlUp).Row
lZeile verwenden, um die letzte Spalte im Bereich zu finden
  lSpalte = Range("XFD" & lZeile).End(xlToLeft).Column
  Set bereich = Range(Cells(1, 1), Cells(lZeile, lSpalte))
'Meldungsbox, um den Bereich anzuzeigen
  MsgBox "Der Bereich ist " & bereich.Address
End Sub

SpecialCells – Letzte Zelle

Wir können auch die SpecialCells-Methode des Range-Objekts verwenden, um die letzte in einem Arbeitsblatt verwendete Zeile und Spalte zu ermitteln.

Sub SpecialCellsVerwenden()
  Dim lZeile As Integer
  Dim lSpalte As Integer
  Dim bereich As Range
  Dim bereichAnfang As Range
  Set bereichAnfang = Range("A1")
  lZeile = bereichAnfang.SpecialCells(xlCellTypeLastCell).Row
  lSpalte = bereichAnfang.SpecialCells(xlCellTypeLastCell).Column
  Set bereich = Range(Cells(1, 1), Cells(lZeile, lSpalte))
'Meldungsbox, um den Bereich anzuzeigen
  MsgBox "Der Bereich ist " & bereich.Address
End Sub

UsedRange

Die UsedRange-Methode umfasst alle Zellen, die im aktuellen Arbeitsblatt Werte enthalten.

Sub UsedRangeBeispiel()
  Dim bereich As Range
  Set bereich = ActiveSheet.UsedRange
'Meldungsbox, um den Bereich anzuzeigen
  MsgBox "Der Bereich ist " & bereich.Address
End Sub

CurrentRegion

Die CurrentRegion unterscheidet sich von der UsedRange-Methode dadurch, dass sie die Zellen rund um eine Zelle, die wir als Startbereich deklariert haben (d. h. die Variable bereichAnfang im folgenden Beispiel), betrachtet und dann alle Zellen, die mit dieser deklarierten Zelle „verbunden“ oder assoziiert sind, berücksichtigt. Sollte eine leere Zelle in einer Zeile oder Spalte vorkommen, hört die CurrentRegion auf, nach weiteren Zellen zu suchen.

Sub AktuelleRegion()
  Dim bereich As Range
  Dim bereichAnfang As Range
  Set bereichAnfang = Range("A1")
  Set bereich = bereichAnfang.CurrentRegion
'Meldungsbox, um den Bereich anzuzeigen
  MsgBox "Bereich ist " & bereich.Address
End Sub

Wenn wir diese Methode verwenden, müssen wir sicherstellen, dass alle Zellen in dem von Ihnen gewünschten Bereich miteinander verbunden sind und keine leeren Zeilen oder Spalten enthalten.

Benannter Bereich

Wir können in unserem Code auch auf benannte Bereiche verweisen. Benannte Bereiche können insofern dynamisch sein, dass sich der Bereichsname beim Aktualisieren oder Einfügen von Daten ändern kann, um die neuen Daten aufzunehmen.

In diesem Beispiel wird die Schriftart für den Bereichsnamen „Januar“ in Fettdruck geändert

Sub BeispielBenannterBereich()
  Dim bereich as Range 
  Set bereich = Range("Januar")
  bereich.Font.Bold = True 
End Sub

Wie Sie in der folgenden Abbildung sehen, wird der Bereichsname automatisch aktualisiert, wenn eine Zeile zum Bereichsnamen hinzugefügt wird.

Sollten wir den Beispielcode erneut ausführen, wäre der vom Code betroffene Bereich C5:C9, während er im ersten Fall C5:C8 gewesen wäre.

Tabellen

Wir können in unserem Code auf Tabellen verweisen (klicken Sie hier für weitere Informationen zum Erstellen und Bearbeiten von Tabellen in VBA). Wenn die Daten einer Tabelle in Excel aktualisiert oder geändert werden, bezieht sich der Code, der auf die Tabelle verweist, auf die aktualisierten Tabellendaten. Dies ist besonders nützlich, wenn auf Pivot-Tabellen, die mit einer externen Datenquelle verbunden sind, verwiesen wird.

Wenn wir diese Tabelle in unserem Code verwenden, können wir auf ihre Spalten durch ihre Überschriften verweisen und Aktionen für die Spalte unter Verwendung ihres Namens durchführen. Wenn die Anzahl der Zeilen in der Tabelle gemäß den Daten zu- oder abnimmt, wird der Tabellenbereich entsprechend angepasst und unser Code funktioniert weiterhin für die gesamte Spalte in der Tabelle. Zum Beispiel:

Sub TabellenSpalteLoeschen()
  ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").ListColumns("Lieferant").Delete
End Sub