Wenn Sie mit Excel arbeiten, verbringen Sie die meiste Zeit im Arbeitsblattbereich - mit Zellen und Bereichen.
Und wenn Sie Ihre Arbeit in Excel mit VBA automatisieren möchten, müssen Sie wissen, wie Sie mit VBA mit Zellen und Bereichen arbeiten.
Es gibt viele verschiedene Dinge, die Sie mit Bereichen in VBA tun können (z. B. Auswählen, Kopieren, Verschieben, Bearbeiten usw.).
Um dieses Thema abzudecken, werde ich dieses Tutorial in Abschnitte unterteilen und Ihnen anhand von Beispielen zeigen, wie Sie mit Zellen und Bereichen in Excel VBA arbeiten.
Lass uns anfangen.
Alle Codes, die ich in diesem Tutorial erwähne, müssen im VB-Editor platziert werden. Gehen Sie zum Abschnitt "Wo soll der VBA-Code platziert werden", um zu erfahren, wie es funktioniert.Wenn Sie daran interessiert sind, VBA auf einfache Weise zu lernen, besuchen Sie mein Online Excel VBA-Schulung.
Auswählen einer Zelle / eines Bereichs in Excel mit VBA
Um mit Zellen und Bereichen in Excel mit VBA zu arbeiten, müssen Sie es nicht auswählen.
In den meisten Fällen ist es besser, keine Zellen oder Bereiche auszuwählen (wie wir sehen werden).
Trotzdem ist es wichtig, dass Sie diesen Abschnitt durchgehen und verstehen, wie er funktioniert. Dies wird für Ihr VBA-Lernen von entscheidender Bedeutung sein und viele der hier behandelten Konzepte werden in diesem Tutorial verwendet.
Beginnen wir also mit einem ganz einfachen Beispiel.
Auswählen einer einzelnen Zelle mit VBA
Wenn Sie eine einzelne Zelle im aktiven Blatt auswählen möchten (z. B. A1), können Sie den folgenden Code verwenden:
Sub SelectCell() Range("A1").Select End Sub
Der obige Code enthält die obligatorischen Teile "Sub" und "End Sub" sowie eine Codezeile, die Zelle A1 auswählt.
Range("A1") teilt VBA die Adresse der Zelle mit, auf die wir uns beziehen möchten.
Auswählen ist eine Methode des Range-Objekts und wählt die im Range-Objekt angegebenen Zellen/Bereiche aus. Die Zellbezüge müssen in doppelte Anführungszeichen gesetzt werden.
Dieser Code würde einen Fehler anzeigen, falls ein Diagrammblatt ein aktives Blatt ist. Ein Diagrammblatt enthält Diagramme und wird nicht häufig verwendet. Da es keine Zellen/Bereiche enthält, kann der obige Code es nicht auswählen und würde am Ende einen Fehler anzeigen.
Beachten Sie, dass Sie nur die Zellenadresse angeben müssen, da Sie die Zelle im aktiven Blatt auswählen möchten.
Wenn Sie jedoch die Zelle in einem anderen Blatt auswählen möchten (z. B. Blatt2), müssen Sie zuerst Blatt2 aktivieren und dann die Zelle darin auswählen.
Sub SelectCell() Worksheets("Sheet2").Activate Range("A1").Select End Sub
Auf ähnliche Weise können Sie auch eine Arbeitsmappe aktivieren, dann ein bestimmtes Arbeitsblatt darin aktivieren und dann eine Zelle auswählen.
Sub SelectCell() Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate Range("A1").Select End Sub
Beachten Sie, dass Sie beim Verweisen auf Arbeitsmappen den vollständigen Namen zusammen mit der Dateierweiterung (.xlsx im obigen Code) verwenden müssen. Falls die Arbeitsmappe noch nie gespeichert wurde, müssen Sie die Dateierweiterung nicht verwenden.
Nun, diese Beispiele sind nicht sehr nützlich, aber Sie werden später in diesem Tutorial sehen, wie wir dieselben Konzepte zum Kopieren und Einfügen von Zellen in Excel (mit VBA) verwenden können.
So wie wir eine Zelle auswählen, können wir auch einen Bereich auswählen.
Bei einem Bereich kann es sich um einen festen Größenbereich oder einen variablen Größenbereich handeln.
In einem festen Größenbereich wissen Sie, wie groß der Bereich ist, und Sie können die genaue Größe in Ihrem VBA-Code verwenden. Aber bei einem Bereich mit variabler Größe haben Sie keine Ahnung, wie groß der Bereich ist, und Sie müssen ein wenig VBA-Magie anwenden.
Mal sehen, wie das geht.
Auswählen eines Bereichs mit fester Größe
Hier ist der Code, der den Bereich A1: D20 auswählt.
Sub SelectRange() Range("A1:D20").Select End Sub
Eine andere Möglichkeit, dies zu tun, ist die Verwendung des folgenden Codes:
Sub SelectRange() Range("A1", "D20").Select End Sub
Der obige Code nimmt die Zelladresse oben links (A1) und die Zelladresse unten rechts (D20) und wählt den gesamten Bereich aus. Diese Technik ist nützlich, wenn Sie mit Bereichen unterschiedlicher Größe arbeiten (wie wir sehen werden, wenn die End-Eigenschaft später in diesem Tutorial behandelt wird).
Wenn die Auswahl in einer anderen Arbeitsmappe oder einem anderen Arbeitsblatt erfolgen soll, müssen Sie VBA die genauen Namen dieser Objekte mitteilen.
Der folgende Code würde beispielsweise den Bereich A1:D20 im Sheet2-Arbeitsblatt in der Book2-Arbeitsmappe auswählen.
Sub SelectRange() Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate Range("A1:D20").Select End Sub
Was ist, wenn Sie nicht wissen, wie viele Zeilen es gibt. Was ist, wenn Sie alle Zellen auswählen möchten, die einen Wert enthalten.
In diesen Fällen müssen Sie die im nächsten Abschnitt (zur Auswahl eines Bereichs mit variabler Größe) gezeigten Methoden verwenden.
Auswählen eines Bereichs unterschiedlicher Größe
Es gibt verschiedene Möglichkeiten, einen Zellbereich auszuwählen. Welche Methode Sie wählen, hängt davon ab, wie die Daten strukturiert sind.
In diesem Abschnitt werde ich einige nützliche Techniken behandeln, die wirklich nützlich sind, wenn Sie mit Bereichen in VBA arbeiten.
Wählen Sie unter Verwendung der CurrentRange-Eigenschaft
In Fällen, in denen Sie nicht wissen, wie viele Zeilen/Spalten die Daten enthalten, können Sie die CurrentRange-Eigenschaft des Range-Objekts verwenden.
Die CurrentRange-Eigenschaft deckt alle zusammenhängenden gefüllten Zellen in einem Datenbereich ab.
Unten ist der Code, der die aktuelle Region auswählt, die Zelle A1 enthält.
Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub
Die obige Methode ist gut, wenn Sie alle Daten als Tabelle ohne leere Zeilen/Spalten haben.
Falls Sie jedoch leere Zeilen/Spalten in Ihren Daten haben, werden die nach den leeren Zeilen/Spalten nicht ausgewählt. In der Abbildung unten wählt der CurrentRegion-Code Daten bis Zeile 10 aus, da Zeile 11 leer ist.
In solchen Fällen möchten Sie möglicherweise die UsedRange-Eigenschaft des Worksheet-Objekts verwenden.
Wählen Sie die UsedRange-Eigenschaft verwenden
Mit UsedRange können Sie auf alle Zellen verweisen, die geändert wurden.
Der folgende Code würde also alle verwendeten Zellen im aktiven Blatt auswählen.
Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub
Beachten Sie, dass für den Fall, dass Sie eine weit entfernte Zelle haben, die verwendet wurde, diese vom obigen Code berücksichtigt wird und alle Zellen bis zu dieser verwendeten Zelle ausgewählt werden.
Wählen Sie mit der End-Eigenschaft
Nun, dieser Teil ist wirklich nützlich.
Mit der Eigenschaft Ende können Sie die letzte gefüllte Zelle auswählen. Auf diese Weise können Sie die Wirkung der Pfeiltasten Strg Ab/Auf oder Strg Rechts/Links nachahmen.
Versuchen wir dies anhand eines Beispiels zu verstehen.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten schnell die zuletzt gefüllten Zellen in Spalte A auswählen.
Das Problem dabei ist, dass sich Daten ändern können und Sie nicht wissen, wie viele Zellen gefüllt sind. Wenn Sie dies mit der Tastatur tun müssen, können Sie Zelle A1 auswählen und dann Strg + Pfeiltaste nach unten verwenden, um die letzte gefüllte Zelle in der Spalte auszuwählen.
Sehen wir uns nun an, wie dies mit VBA geht. Diese Technik ist praktisch, wenn Sie schnell zur letzten gefüllten Zelle in einer Spalte unterschiedlicher Größe springen möchten
Sub GoToLastFilledCell() Range("A1").End(xlDown).Select End Sub
Der obige Code würde zur letzten gefüllten Zelle in Spalte A springen.
Ebenso können Sie End(xlToRight) verwenden, um zur letzten gefüllten Zelle in einer Zeile zu springen.
Sub GoToLastFilledCell() Range("A1").End(xlToRight).Select End Sub
Was ist nun, wenn Sie die gesamte Spalte auswählen möchten, anstatt zur letzten gefüllten Zelle zu springen.
Sie können dies mit dem folgenden Code tun:
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Select End Sub
Im obigen Code haben wir die erste und die letzte Referenz der Zelle verwendet, die wir auswählen müssen. Egal wie viele gefüllte Zellen vorhanden sind, der obige Code wählt alle aus.
Erinnern Sie sich an das obige Beispiel, in dem wir den Bereich A1:D20 mit der folgenden Codezeile ausgewählt haben:
Reichweite("A1″",D20")
Hier war A1 die obere linke Zelle und D20 die untere rechte Zelle im Bereich. Wir können dieselbe Logik bei der Auswahl von Bereichen unterschiedlicher Größe verwenden. Da wir jedoch die genaue Adresse der Zelle unten rechts nicht kennen, haben wir die End-Eigenschaft verwendet, um sie abzurufen.
In Range("A1", Range("A1").End(xlDown)) bezieht sich "A1" auf die erste Zelle und Range("A1").End(xlDown) bezieht sich auf die letzte Zelle. Da wir beide Referenzen bereitgestellt haben, wählt die Select-Methode alle Zellen zwischen diesen beiden Referenzen aus.
Auf ähnliche Weise können Sie auch einen gesamten Datensatz mit mehreren Zeilen und Spalten auswählen.
Der folgende Code würde alle gefüllten Zeilen/Spalten ab Zelle A1 auswählen.
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select End Sub
Im obigen Code haben wir Range(“A1”).End(xlDown).End(xlToRight) verwendet, um die Referenz der unteren rechten gefüllten Zelle des Datensatzes zu erhalten.
Unterschied zwischen der Verwendung von CurrentRegion und End
Wenn Sie sich fragen, warum Sie die End-Eigenschaft verwenden, um den gefüllten Bereich auszuwählen, wenn wir die CurrentRegion-Eigenschaft haben, lassen Sie mich Ihnen den Unterschied erklären.
Mit der End-Eigenschaft können Sie die Startzelle angeben. Wenn Sie beispielsweise Ihre Daten in A1:D20 haben, die erste Zeile jedoch Kopfzeilen enthält, können Sie die End-Eigenschaft verwenden, um die Daten ohne Kopfzeilen auszuwählen (mithilfe des folgenden Codes).
Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select End Sub
Aber die CurrentRegion würde automatisch das gesamte Dataset auswählen, einschließlich der Header.
Bisher haben wir in diesem Tutorial gesehen, wie Sie auf verschiedene Weise auf einen Bereich von Zellen verweisen.
Sehen wir uns nun einige Möglichkeiten an, wie wir diese Techniken tatsächlich verwenden können, um etwas Arbeit zu erledigen.
Zellen / Bereiche mit VBA kopieren
Wie ich zu Beginn dieses Tutorials erwähnt habe, ist die Auswahl einer Zelle nicht erforderlich, um Aktionen darauf auszuführen. In diesem Abschnitt erfahren Sie, wie Sie Zellen und Bereiche kopieren, ohne diese auszuwählen.
Beginnen wir mit einem einfachen Beispiel.
Einzelne Zelle kopieren
Wenn Sie Zelle A1 kopieren und in Zelle D1 einfügen möchten, würde der folgende Code dies tun.
Sub CopyCell() Range("A1").Copy Range("D1") End Sub
Beachten Sie, dass die Kopiermethode des Bereichsobjekts die Zelle kopiert (genau wie Strg +C) und am angegebenen Ziel einfügt.
Im obigen Beispielcode wird das Ziel in derselben Zeile angegeben, in der Sie die Methode Copy verwenden. Wenn Sie Ihren Code noch lesbarer machen möchten, können Sie den folgenden Code verwenden:
Sub CopyCell() Range("A1").Copy Destination:=Range("D1") End Sub
Die obigen Codes kopieren und fügen den Wert sowie die Formatierung/Formeln darin ein.
Wie Sie vielleicht bereits bemerkt haben, kopiert der obige Code die Zelle, ohne sie auszuwählen. Egal wo Sie sich auf dem Arbeitsblatt befinden, der Code kopiert Zelle A1 und fügt sie in D1 ein.
Beachten Sie außerdem, dass der obige Code jeden vorhandenen Code in Zelle D2 überschreiben würde. Wenn Sie möchten, dass Excel Sie mitteilt, ob sich bereits etwas in Zelle D1 befindet, ohne es zu überschreiben, können Sie den folgenden Code verwenden.
Sub CopyCell() If Range("D1") "" Then Response = MsgBox("Möchten Sie die vorhandenen Daten überschreiben", vbYesNo) End If Response = vbYes Then Range("A1").Copy Range("D1 ") Ende Wenn Ende Unter
Kopieren eines Bereichs mit fester Größe
Wenn Sie A1: D20 in J1: M20 kopieren möchten, können Sie den folgenden Code verwenden:
Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub
In der Zielzelle müssen Sie nur die Adresse der Zelle oben links angeben. Der Code würde automatisch den genau kopierten Bereich in das Ziel kopieren.
Sie können dasselbe Konstrukt verwenden, um Daten von einem Blatt in das andere zu kopieren.
Der folgende Code würde A1:D20 aus dem aktiven Blatt in Sheet2 kopieren.
Sub CopyRange() Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Das obige kopiert die Daten aus dem aktiven Blatt. Stellen Sie also sicher, dass das Blatt mit den Daten das aktive Blatt ist, bevor Sie den Code ausführen. Zur Sicherheit können Sie beim Kopieren der Daten auch den Namen des Arbeitsblatts angeben.
Sub CopyRange() Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Das Gute an dem obigen Code ist, dass unabhängig davon, welches Blatt aktiv ist, die Daten immer aus Sheet1 kopiert und in Sheet2 eingefügt werden.
Sie können einen benannten Bereich auch kopieren, indem Sie seinen Namen anstelle der Referenz verwenden.
Wenn Sie beispielsweise einen benannten Bereich namens "SalesData" haben, können Sie den folgenden Code verwenden, um diese Daten in Sheet2 zu kopieren.
Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub
Wenn der Bereich des benannten Bereichs die gesamte Arbeitsmappe umfasst, müssen Sie sich nicht auf dem Blatt befinden, das den benannten Bereich enthält, um diesen Code auszuführen. Da der benannte Bereich für die Arbeitsmappe gilt, können Sie mit diesem Code von jedem Blatt aus darauf zugreifen.
Wenn Sie eine Tabelle mit dem Namen Table1 haben, können Sie den folgenden Code verwenden, um sie in Sheet2 zu kopieren.
Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sheet2").Range("A1") End Sub
Sie können einen Bereich auch in eine andere Arbeitsmappe kopieren.
Im folgenden Beispiel kopiere ich die Excel-Tabelle (Tabelle1) in die Arbeitsmappe Book2.
Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub
Dieser Code würde nur funktionieren, wenn die Arbeitsmappe bereits geöffnet ist.
Kopieren eines Bereichs variabler Größe
Eine Möglichkeit, Bereiche mit variabler Größe zu kopieren, besteht darin, diese in benannte Bereiche oder Excel-Tabellen zu konvertieren und die Codes wie im vorherigen Abschnitt gezeigt zu verwenden.
Wenn dies jedoch nicht möglich ist, können Sie die CurrentRegion- oder die End-Eigenschaft des Bereichsobjekts verwenden.
Der folgende Code würde den aktuellen Bereich im aktiven Blatt kopieren und in Sheet2 einfügen.
Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub
Wenn Sie die erste Spalte Ihres Datensatzes bis zur letzten gefüllten Zelle kopieren und in Sheet2 einfügen möchten, können Sie den folgenden Code verwenden:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1") End Sub
Wenn Sie sowohl die Zeilen als auch die Spalten kopieren möchten, können Sie den folgenden Code verwenden:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1") End Sub
Beachten Sie, dass all diese Codes die Zellen nicht auswählen, während sie ausgeführt werden. Im Allgemeinen werden Sie nur eine Handvoll Fälle finden, in denen Sie tatsächlich eine Zelle/einen Bereich auswählen müssen, bevor Sie daran arbeiten.
Zuweisen von Bereichen zu Objektvariablen
Bisher haben wir die vollständige Adresse der Zellen verwendet (z. B. Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")).
Um Ihren Code leichter handhabbar zu machen, können Sie diese Bereiche Objektvariablen zuweisen und dann diese Variablen verwenden.
Im folgenden Code habe ich beispielsweise den Quell- und Zielbereich Objektvariablen zugewiesen und dann diese Variablen verwendet, um Daten von einem Bereich in den anderen zu kopieren.
Sub CopyRange() Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets("Sheet1").Range("A1:D20") Set DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange End Sub
Wir beginnen damit, die Variablen als Range-Objekte zu deklarieren. Dann weisen wir diesen Variablen mit der Set-Anweisung den Bereich zu. Nachdem der Variable der Bereich zugewiesen wurde, können Sie die Variable einfach verwenden.
Geben Sie Daten in die nächste leere Zelle ein (über das Eingabefeld)
Sie können die Eingabefelder verwenden, um dem Benutzer die Eingabe der Daten zu ermöglichen.
Angenommen, Sie haben den folgenden Datensatz und möchten den Verkaufsdatensatz eingeben, können Sie das Eingabefeld in VBA verwenden. Mit einem Code können wir sicherstellen, dass die Daten in der nächsten leeren Zeile ausgefüllt werden.
Sub EnterData() Dim RefRange As Range Set RefRange = Range("A1").End(xlDown).Offset(1, 0) Set ProductCategory = RefRange.Offset(0, 1) Set Quantity = RefRange.Offset(0, 2 ) Set Amount = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Produktkategorie") Menge.Value = InputBox("Quantity") Betrag.Wert = InputBox("Betrag") End Sub
Der obige Code verwendet das VBA-Eingabefeld, um die Eingaben vom Benutzer abzurufen, und gibt die Eingaben dann in die angegebenen Zellen ein.
Beachten Sie, dass wir keine exakten Zellbezüge verwendet haben. Stattdessen haben wir die Eigenschaft End und Offset verwendet, um die letzte leere Zelle zu finden und die Daten darin zu füllen.
Dieser Code ist alles andere als brauchbar. Wenn Sie beispielsweise eine Textzeichenfolge eingeben, wenn das Eingabefeld nach Menge oder Menge fragt, werden Sie feststellen, dass Excel dies zulässt. Sie können eine If-Bedingung verwenden, um zu überprüfen, ob der Wert numerisch ist oder nicht, und ihn dann entsprechend zulassen.
Durchlaufen von Zellen / Bereichen
Bisher haben wir gesehen, wie Sie die Daten in Zellen und Bereichen auswählen, kopieren und eingeben.
In diesem Abschnitt werden wir sehen, wie Sie eine Reihe von Zellen/Zeilen/Spalten in einem Bereich durchlaufen. Dies kann nützlich sein, wenn Sie jede Zelle analysieren und eine darauf basierende Aktion ausführen möchten.
Wenn Sie beispielsweise jede dritte Zeile in der Auswahl hervorheben möchten, müssen Sie die Zeilennummer durchlaufen und überprüfen. Wenn Sie alle negativen Zellen hervorheben möchten, indem Sie die Schriftfarbe in Rot ändern, müssen Sie den Wert jeder Zelle durchlaufen und analysieren.
Hier ist der Code, der die Zeilen in den ausgewählten Zellen durchläuft und alternative Zeilen hervorhebt.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Auswahl für jede Myrow in Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Der obige Code verwendet die MOD-Funktion, um die Zeilennummer in der Auswahl zu überprüfen. Wenn die Zeilennummer gerade ist, wird sie in Cyan hervorgehoben.
Hier ist ein weiteres Beispiel, bei dem der Code jede Zelle durchläuft und die Zellen mit einem negativen Wert hervorhebt.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Mycell As Range Set Myrange = Auswahl für jede Mycell in Myrange If Mycell < 0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Beachten Sie, dass Sie dasselbe mit der bedingten Formatierung tun können (die dynamisch ist und eine bessere Möglichkeit bietet). Dieses Beispiel dient nur dazu, Ihnen zu zeigen, wie Schleifen mit Zellen und Bereichen in VBA funktioniert.
Wo soll der VBA-Code abgelegt werden?
Sie fragen sich, wo der VBA-Code in Ihrer Excel-Arbeitsmappe hingehört?
Excel hat ein VBA-Backend namens VBA-Editor. Sie müssen den Code kopieren und in das Codefenster des VB-Editor-Moduls einfügen.
Hier sind die Schritte, um dies zu tun:
- Wechseln Sie zur Registerkarte Entwickler.
- Klicken Sie auf die Visual Basic-Option. Dies öffnet den VB-Editor im Backend.
- Klicken Sie im Bereich Projekt-Explorer im VB-Editor mit der rechten Maustaste auf ein beliebiges Objekt für die Arbeitsmappe, in die Sie den Code einfügen möchten.Wenn Sie den Projekt-Explorer nicht sehen, gehen Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
- Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird ein Modulobjekt für Ihre Arbeitsmappe eingefügt.
- Kopieren Sie den Code und fügen Sie ihn in das Modulfenster ein.
