Arbeiten mit Zellen und Bereichen in Excel VBA (Auswählen, Kopieren, Verschieben, Bearbeiten)
Bei der Arbeit mit Excel wird die meiste Zeit im Arbeitsblattbereich verbracht – mit Zellen und Bereichen.
Und wenn Sie Ihre Arbeit in Excel mit VBA automatisieren möchten, müssen Sie wissen, wie Sie mit Zellen und Bereichen mit VBA 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 zu behandeln, werde ich dieses Tutorial in Abschnitte unterteilen und Ihnen anhand von Beispielen zeigen, wie Sie mit Zellen und Bereichen in Excel VBA arbeiten.,
Los geht ‚ s.
Wenn Sie daran interessiert sind, VBA auf einfache Weise zu lernen, schauen Sie sich mein Online-Excel-VBA-Training an.
Dieses Tutorial behandelt:
Auswählen einer Zelle/eines Bereichs in Excel mit VBA
Um mit Zellen und Bereichen in Excel mit VBA zu arbeiten, müssen Sie sie 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 es funktioniert. Dies wird für Ihr VBA-Lernen von entscheidender Bedeutung sein und viele hier behandelte Konzepte werden in diesem Tutorial verwendet.
Beginnen wir also mit einem sehr 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").SelectEnd Sub
Der obige Code enthält den obligatorischen Teil ‚Sub‘ und ‚End Sub‘ sowie eine Codezeile, die Zelle A1 auswählt.,
Range („A1“) teilt VBA die Adresse der Zelle mit, auf die wir verweisen möchten.
Select ist eine Methode des Bereichsobjekts und wählt die im Bereichsobjekt angegebenen Zellen/Bereiche aus. Die Zellreferenzen 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 ist nicht weit verbreitet. Da es keine Zellen / Bereiche enthält, kann der obige Code es nicht auswählen und zeigt am Ende einen Fehler an.
Beachten Sie, dass Sie, da Sie die Zelle im aktiven Blatt auswählen möchten, nur die Zellenadresse angeben müssen.,
Wenn Sie jedoch die Zelle in einem anderen Blatt auswählen möchten (z. B. Sheet2), müssen Sie zuerst Sheet2 aktivieren und dann die Zelle darin auswählen.
Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
Ebenso 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").ActivateRange("A1").SelectEnd Sub
Beachten Sie, dass Sie beim Verweisen auf Arbeitsmappen den vollständigen Namen zusammen mit der Dateierweiterung (.xlsx im obigen code). Falls die Arbeitsmappe 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 verwenden können (mit VBA).
So wie wir eine Zelle auswählen, können wir auch einen Bereich auswählen.
Im Falle eines Bereichs 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. Bei einem Bereich mit variabler Größe haben Sie jedoch keine Ahnung, wie groß der Bereich ist, und Sie müssen ein wenig VBA-Magie verwenden.
Mal sehen, wie das geht.,
Auswahl eines Bereichs mit fester Größe
Hier ist der Code, der den Bereich A1:D20 auswählt.
Sub SelectRange()Range("A1:D20").SelectEnd Sub
Eine andere Möglichkeit ist die Verwendung des folgenden Codes:
Sub SelectRange()Range("A1", "D20").SelectEnd Sub
Der obige Code verwendet die Adresse der oberen linken Zelle (A1) und die Adresse der unteren rechten Zelle (D20) und wählt den gesamten Bereich aus. Diese Technik wird 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").ActivateRange("A1:D20").SelectEnd Sub
Nun, was, wenn Sie nicht wissen, wie viele Zeilen vorhanden sind. 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 gezeigten Methoden verwenden (bei Auswahl eines Bereichs mit variabler Größe).,
Auswahl eines variabel dimensionierten Bereichs
Es gibt verschiedene Möglichkeiten, einen Zellbereich auszuwählen. Die von Ihnen gewählte Methode 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 Mit CurrentRange Eigenschaft
In Fällen, in denen Sie nicht wissen, wie viele Zeilen/Spalten die Daten haben, können Sie die CurrentRange Eigenschaft des Bereichsobjekts verwenden.
Die Eigenschaft CurrentRange deckt alle zusammenhängend gefüllten Zellen in einem Datenbereich ab.,
Unten ist der Code, der den aktuellen Bereich auswählt, der Zelle A1 enthält.
Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd 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. Im Bild unten wählt der CurrentRegion-Code Daten aus, bis Zeile 10 leer ist, da Zeile 11 leer ist.
In solchen Fällen möchten Sie möglicherweise die UsedRange-Eigenschaft des Arbeitsblattobjekts verwenden.,
Wählen Sie UsedRange Eigenschaft
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.SelectEnd Sub
Beachten Sie, dass für den Fall, dass Sie eine weit entfernte Zelle haben, die verwendet wurde, diese vom obigen Code und allen Zellen berücksichtigt wird, bis diese Zelle ausgewählt ist.
Wählen Sie mit der End-Eigenschaft
Jetzt ist dieser Teil wirklich nützlich.
Mit der Eigenschaft End können Sie die zuletzt gefüllte Zelle auswählen., Auf diese Weise können Sie den Effekt der Pfeiltaste nach unten/oben oder der rechten/linken Taste 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 hier ist, dass sich Daten ändern können und Sie nicht wissen, wie viele Zellen gefüllt sind. Wenn Sie dies über die Tastatur tun müssen, können Sie Zelle A1 auswählen und dann Strg + Abwärtspfeiltaste verwenden, um die zuletzt ausgefüllte Zelle in der Spalte auszuwählen.
Nun wollen wir sehen, wie dies mit VBA zu tun., Diese Technik ist praktisch, wenn Sie schnell zur letzten gefüllten Zelle in einer unterschiedlich großen Spalte springen möchten
Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub
Der obige Code springt zur letzten gefüllten Zelle in Spalte A.
In ähnlicher Weise können Sie mit dem Ende(xlToRight) zur letzten gefüllten Zelle in einer Zeile springen.
Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub
Was ist nun, wenn Sie die gesamte Spalte auswählen möchten, anstatt zur zuletzt gefüllten Zelle zu springen?,
Sie können dies mit dem folgenden Code tun:
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd 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.
Denken Sie an das obige Beispiel, in dem wir den Bereich A1:D20 mithilfe der folgenden Codezeile ausgewählt haben:
Bereich(„A1″,“D20“)
Hier war A1 die Zelle oben links und D20 die Zelle unten rechts im Bereich. Wir können die gleiche Logik bei der Auswahl variabel großer Bereiche verwenden., Da wir jedoch die genaue Adresse der Zelle unten rechts nicht kennen, haben wir die End-Eigenschaft verwendet, um sie abzurufen.
Im Bereich („A1“, Bereich („A1″).End (xlDown)),“ A1″bezieht sich auf die erste Zelle und den ersten Bereich („A1“).Ende(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.
In ähnlicher 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)).SelectEnd Sub
Im obigen code haben wir verwendet, Range(„A1“).Ende(xlDown).Ende (xlToRight), um die Referenz der unten rechts gefüllten Zelle des Datensatzes abzurufen.
Unterschied zwischen der Verwendung von CurrentRegion und End
Wenn Sie sich fragen, warum Sie die Eigenschaft End verwenden, um den ausgefüllten Bereich auszuwählen, wenn wir die Eigenschaft CurrentRegion haben, lassen Sie mich Ihnen den Unterschied mitteilen.
Mit der Eigenschaft End können Sie die Startzelle angeben., Wenn Sie beispielsweise Ihre Daten in A1:D20 haben, die erste Zeile jedoch Header sind, können Sie die Eigenschaft End verwenden, um die Daten ohne Header auszuwählen (mithilfe des folgenden Codes).
Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub
Aber die CurrentRegion würde automatisch den gesamten Datensatz einschließlich der Header auswählen.
Bisher haben wir in diesem Tutorial gesehen, wie man auf verschiedene Arten auf eine Reihe von Zellen verweist.
Lassen Sie uns nun einige Möglichkeiten sehen, wie wir diese Techniken tatsächlich verwenden können, um etwas Arbeit zu erledigen.,
Kopieren Sie Zellen / Bereiche mit VBA
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 Control +C) und in das angegebene Ziel einfügt.,
Im obigen Beispielcode wird das Ziel in derselben Zeile angegeben, in der Sie die Kopiermethode 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 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 auch, dass der obige Code vorhandenen Code in Zelle D2 überschreiben würde., Wenn Sie möchten, dass Excel Sie darüber informiert, ob sich bereits etwas in Zelle D1 befindet, ohne es zu überschreiben, können Sie den folgenden Code verwenden.
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 kopiert automatisch den exakt kopierten Bereich in das Ziel.
Sie können dasselbe Konstrukt verwenden, um Daten von einem Blatt auf das andere zu kopieren.
Der folgende Code würde A1:D20 vom aktiven Blatt nach 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. Um sicherzugehen, können Sie auch den Namen des Arbeitsblatts beim Kopieren der Daten angeben.
Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
Das Gute am obigen Code ist, dass unabhängig davon, welches Blatt aktiv ist, die Daten immer von Sheet1 kopiert und in Sheet2 eingefügt werden.
Sie können auch einen benannten Bereich kopieren, indem Sie dessen 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 ist, müssen Sie sich nicht auf dem Blatt befinden, das den benannten Bereich hat, um diesen Code auszuführen. Da der benannte Bereich für die Arbeitsmappe festgelegt ist, können Sie mit diesem Code von jedem Blatt aus darauf zugreifen.
Wenn Sie eine Tabelle mit dem Namen Table1 haben, können Sie sie mit dem folgenden Code in Sheet2 kopieren.
Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub
Sie können auch einen Bereich in eine andere Arbeitsmappe kopieren.,
Im folgenden Beispiel kopiere ich die Excel-Tabelle (Tabelle1) in die Book2-Arbeitsmappe.
Sub CopyCurrentRegion()Range("Table1").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 variabler Größe zu kopieren, besteht darin, diese in benannte Bereiche oder Excel-Tabellen umzuwandeln und die im vorherigen Abschnitt gezeigten Codes zu verwenden.
Wenn Sie dies jedoch nicht können, können Sie die Eigenschaft CurrentRegion oder die Eigenschaft End des Bereichsobjekts verwenden.
Der folgende Code kopiert den aktuellen Bereich im aktiven Blatt und fügt ihn in Sheet2 ein.,
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 die Zeilen sowie 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 alle diese Codes wählen die Zellen nicht aus, während sie ausgeführt werden. Im Allgemeinen finden Sie nur eine Handvoll Fälle, 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. Arbeitsmappen („Book2.xlsx“).Arbeitsblätter („Blatt1“).Range(„A1“)).
Um Ihren Code besser handhabbar zu machen, können Sie diese Bereiche Objektvariablen zuweisen und diese Variablen dann verwenden.
Zum Beispiel habe ich im folgenden Code den Quell-und Zielbereich Objektvariablen zugewiesen und diese Variablen dann zum Kopieren von Daten von einem Bereich in den anderen verwendet.
Zunächst deklarieren wir die Variablen als Bereichsobjekte., Dann weisen wir diesen Variablen den Bereich mit der Set-Anweisung zu. Sobald der Bereich der Variablen zugewiesen wurde, können Sie einfach die Variable verwenden.
Geben Sie Daten in die nächste leere Zelle ein (mit Eingabefeld)
Sie können die Eingabefelder verwenden, damit der Benutzer die Daten eingeben kann.
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.,
Der obige Code verwendet das VBA-Eingabefeld, um die Eingaben vom Benutzer abzurufen, und gibt dann die Eingaben in die angegebenen Zellen ein.
Beachten Sie, dass wir keine genauen Zellreferenzen 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 bei weitem nicht verwendbar. Wenn Sie beispielsweise eine Textzeichenfolge eingeben, wenn das Eingabefeld nach Menge oder Betrag 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 die Daten in Zellen und Bereichen ausgewählt, kopiert und eingegeben werden.
In diesem Abschnitt erfahren Sie, wie Sie eine Reihe von Zellen/Zeilen/Spalten in einem Bereich durchlaufen. Dies kann nützlich sein, wenn Sie jede Zelle analysieren und darauf basierende Aktionen ausführen möchten.
Wenn Sie beispielsweise jede dritte Zeile in der Auswahl markieren möchten, müssen Sie eine Schleife durchlaufen und nach der Zeilennummer suchen., 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.
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, in dem der Code jede Zelle durchläuft und die Zellen hervorhebt, die einen negativen Wert enthalten.,
Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub
Beachten Sie, dass Sie dasselbe mit bedingter Formatierung tun können (was dynamisch und eine bessere Möglichkeit ist). Dieses Beispiel dient nur dazu, Ihnen zu zeigen, wie das Schleifen mit Zellen und Bereichen in VBA funktioniert.
Wo kann ich den VBA-Code ablegen
Sie fragen sich, wohin der VBA-Code in Ihrer Excel-Arbeitsmappe geht?
Excel verfügt über ein VBA-Backend namens VBA-Editor. Sie müssen den Code kopieren und in das VB-Editor-Modul-Code-Fenster einfügen.
Hier sind die Schritte dazu:
- Gehen Sie zur Registerkarte Entwickler.,
- Klicken Sie auf die Option Visual Basic. Dadurch wird der VB-Editor im Backend geöffnet.
- Klicken Sie im Projekt-Explorer-Bereich 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, wechseln Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
- Gehe zum Einfügen und klicke 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.,
Möglicherweise mögen Sie auch die folgenden Excel-Tutorials:
- Arbeiten mit Arbeitsblättern mit VBA.
- Arbeiten mit Arbeitsmappen mit VBA.
- Benutzerdefinierte Funktionen in Excel erstellen.
- Für die nächste Schleife in Excel VBA – Ein Leitfaden für Anfänger mit Beispielen.
- Verwendung der Excel VBA InStr-Funktion (mit praktischen BEISPIELEN).
- Excel VBA Msgbox.
- So zeichnen Sie ein Makro in Excel auf.
- So führen Sie ein Makro in Excel aus.
- so Erstellen Sie ein Add-in in Excel.,
- Excel Persönlichen Makro-Arbeitsmappe | Speichern & Verwenden Sie die Makros in Allen Arbeitsmappen.
- Excel VBA Events – Eine Einfache (und Vollständige) Anleitung.
- Excel-VBA-Fehlerbehandlung.
- So sortieren Sie Daten in Excel mit VBA (Eine Schritt-für-Schritt-Anleitung).
- 24 Nützliche Excel Makro Beispiele für VBA Anfänger (Ready-to-use).