In diesem Artikel wird gezeigt, wie Sie einen dynamischen Bereich in Excel VBA erstellen.
Das Deklarieren eines bestimmten Zellbereichs als Variable in Excel VBA beschränkt uns darauf, nur mit diesen bestimmten Zellen zu arbeiten. Durch die Deklaration von Dynamic Ranges in Excel gewinnen wir viel mehr Flexibilität in Bezug auf unseren Code und die Funktionalität, die er ausführen kann.
Referenzieren von Bereichen und Zellen
Wenn wir in Excel auf das Range- oder Cell-Objekt verweisen, verweisen wir normalerweise darauf, indem wir die erforderlichen Zeilen und Spalten fest codieren.
Bereichseigenschaft
Mit der Range-Eigenschaft können wir in den folgenden Beispielcodezeilen Aktionen für diesen Bereich ausführen, z. B. die Farbe der Zellen ändern oder die Zellen fett formatieren.
| 12 | Range("A1:A5").Font.Color = vbRedRange("A1:A5").Font.Bold = True |
Zelleneigenschaft
Ebenso können wir die Cells-Eigenschaft verwenden, um auf einen Zellbereich zu verweisen, indem wir direkt auf die Zeile und Spalte in der Zelleneigenschaft verweisen. Die Zeile muss immer eine Zahl sein, aber die Spalte kann eine Zahl oder ein in Anführungszeichen eingeschlossener Buchstabe sein.
Die Zellenadresse A1 kann beispielsweise wie folgt referenziert werden:
| 1 | Zellen(1,1) |
Oder
| 1 | Zellen(1, "A") |
Um die Cells-Eigenschaft zu verwenden, um auf einen Zellbereich zu verweisen, müssen wir den Anfang des Bereichs und das Ende des Bereichs angeben.
Zum Beispiel für den Referenzbereich A1: A6 könnten wir diese Syntax unten verwenden:
| 1 | Bereich(Zellen(1,1), Zellen(1,6) |
Wir können dann die Cells-Eigenschaft verwenden, um Aktionen für den Bereich gemäß den folgenden Beispielcodezeilen auszuführen:
| 12 | Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRedRange(Cells(2, 2), Cells(6, 2)).Font.Bold = True |
Dynamikbereiche mit Variablen
Da sich die Größe unserer Daten in Excel ändert (d. h. wir verwenden mehr Zeilen und Spalten als die von uns codierten Bereiche), wäre es nützlich, wenn sich auch die Bereiche ändern würden, auf die wir uns in unserem Code beziehen. Mit dem obigen Range-Objekt können wir Variablen erstellen, um die maximalen Zeilen- und Spaltenzahlen 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
| 1234 | Dim lRow als GanzzahlDim lCol als GanzzahllRow = Range("A1048576").End(xlUp).RowlCol = Range("XFD1").End(xlToLeft).Column |
Letzte Zeile in Spalte
Da ein Arbeitsblatt 1048576 Zeilen enthält, wird die Variable lRow an das Ende des Blatts verschoben und verwendet dann die spezielle Kombination der Endtaste plus der Aufwärtspfeiltaste, um zur letzten im Arbeitsblatt verwendeten Zeile zu gelangen - dies gibt uns die Nummer der Zeile, die wir in unserem Sortiment benötigen.
Letzte Spalte in Zeile
In ähnlicher Weise bewegt sich die lCol zu Spalte XFD, die die letzte Spalte in einem Arbeitsblatt ist, und verwendet dann die spezielle Tastenkombination der End-Taste plus die linke Pfeiltaste, um zur letzten Spalte des Arbeitsblatts zu gehen - dies gibt uns die Nummer der Spalte, die wir in unserem Sortiment benötigen.
Um den gesamten Bereich zu erhalten, der im Arbeitsblatt verwendet wird, können wir daher den folgenden Code ausführen:
| 1234567891011 | Unter GetRange()Dim lRow As IntegerDim lCol As IntegerDim rng As RangelRow = Range("A1048576").End(xlUp).Row'Verwenden Sie die lRow, um die letzte Spalte im Bereich zu findenlCol = Range("XFD" & lRow).End(xlToLeft).ColumnSet rng = Range(Cells(1, 1), Cells(lRow, lCol))'msgbox um uns die Reichweite zu zeigenMsgBox "Bereich ist" & rng.AdresseEnd Sub |
Spezialzellen - LastCell
Wir können auch die SpecialCells-Methode des Range-Objekts verwenden, um die letzte Zeile und Spalte abzurufen, die in einem Arbeitsblatt verwendet werden.
| 123456789101112 | Sub UseSpecialCells()Dim lRow As IntegerDim lCol As IntegerDim rng As RangeDim rngBegin As RangeSetze rngBegin = Range("A1")lRow = rngBegin.SpecialCells(xlCellTypeLastCell).RowlCol = rngBegin.SpecialCells(xlCellTypeLastCell).ColumnSet rng = Range(Cells(1, 1), Cells(lRow, lCol))'msgbox um uns die Reichweite zu zeigenMsgBox "Bereich ist" & rng.AdresseEnd Sub |
GebrauchtSortiment
Die Methode "Benutzter Bereich" enthält alle Zellen, die Werte im aktuellen Arbeitsblatt enthalten.
| 123456 | Unter UsedRangeExample()Dim rng As RangeSet rng = ActiveSheet.UsedRange'msgbox um uns die Reichweite zu zeigenMsgBox "Bereich ist" & rng.AdresseEnd Sub |
AktuelleRegion
Der aktuelle Bereich unterscheidet sich vom UsedRange dadurch, dass er sich die Zellen ansieht, die eine Zelle umgeben, die wir als Startbereich deklariert haben (dh die Variable rngBegin im Beispiel unten) und dann alle Zellen betrachtet, die „angehängt“ oder verknüpft sind in die deklarierte Zelle. Sollte eine leere Zelle in einer Zeile oder Spalte auftreten, wird die CurrentRegion aufhören, nach weiteren Zellen zu suchen.
| 12345678 | Sub CurrentRegion()Dim rng As RangeDim rngBegin As RangeSetze rngBegin = Range("A1")Setze rng = rngBegin.CurrentRegion'msgbox um uns die Reichweite zu zeigenMsgBox "Bereich ist" & rng.AdresseEnd Sub |
Wenn wir diese Methode verwenden, müssen wir sicherstellen, dass alle Zellen in dem von Ihnen benötigten Bereich ohne leere Zeilen oder Spalten verbunden sind.
Benannter Bereich
Wir können in unserem Code auch auf benannte Bereiche verweisen. Benannte Bereiche können insofern dynamisch sein, als beim Aktualisieren oder Einfügen von Daten der Bereichsname geändert werden kann, um die neuen Daten einzuschließen.
In diesem Beispiel wird die Schriftart für den Bereichsnamen „Januar“ in Fettdruck geändert.
| 12345 | Sub RangeNameExample()Dim rng als RangeSet rng = Range("Januar")rng.Font.Bold = = TrueEnd Sub |
Wie Sie in der Abbildung unten sehen werden, wird der Bereichsname automatisch aktualisiert, um diese Zeile einzuschließen, wenn eine Zeile zum Bereichsnamen hinzugefügt wird.
Wenn wir den Beispielcode dann erneut ausführen, wäre der vom Code betroffene Bereich C5:C9, während es zunächst C5:C8 gewesen wäre.
Tabellen
Wir können in unserem Code auf Tabellen verweisen (klicken Sie hier, um weitere Informationen zum Erstellen und Bearbeiten von Tabellen in VBA zu erhalten). Wenn Tabellendaten in Excel aktualisiert oder geändert werden, verweist der Code, der auf die Tabelle verweist, auf die aktualisierten Tabellendaten. Dies ist besonders nützlich, wenn auf Pivot-Tabellen verwiesen wird, die mit einer externen Datenquelle verbunden sind.
Wenn wir diese Tabelle in unserem Code verwenden, können wir anhand der Überschriften in jeder Spalte auf die Spalten der Tabelle verweisen und Aktionen für die Spalte entsprechend ihrem Namen ausführen. Da sich die Zeilen in der Tabelle entsprechend den Daten vergrößern oder verkleinern, wird der Tabellenbereich entsprechend angepasst und unser Code funktioniert weiterhin für die gesamte Spalte in der Tabelle.
Zum Beispiel:
| 123 | Unter DeleteTableColumn()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListColumns("Lieferant").DeleteEnd Sub |
