Excel VBA – Bereiche und Zellen

Bereiche und Zellen in VBA

In Excel-Tabellenblättern werden Daten in Zellen gespeichert. Die Zellen sind in Zeilen und Spalten angeordnet. Jede Zelle kann durch den Schnittpunkt ihrer Zeile und Spalte identifiziert werden (Bsp. B3 oder R3C2).

Ein Excel-Bereich bezieht sich auf eine oder mehrere Zellen (z. B. A3:B4).

Zellenadresse

A1-Schreibweise

In der A1-Schreibweise wird eine Zelle durch ihren Spaltenbuchstaben (von A bis XFD) gefolgt von ihrer Zeilennummer (von 1 bis 1.048.576) bezeichnet. Dies wird als Zellenadresse bezeichnet.

In VBA können Sie mit dem Range-Objekt auf jede Zelle verweisen.

' Auf die Zelle B4 auf dem aktuell aktiven Blatt verweisen
MsgBox Range("B4")

' Auf die Zelle B4 auf dem Blatt "Daten" verweisen
MsgBox Worksheets("Daten").Range("B4")

' Auf Zelle B4 auf dem Blatt mit dem Namen 'Daten' in einer anderen OFFENEN Arbeitsmappe mit dem Namen 'Meine Daten verweisen

MsgBox Worksheets("Meine Daten").Worksheets("Daten").Range("B4")

R1C1-Schreibweise

In der R1C1-Schreibweise wird eine Zelle mit R, gefolgt von der Zeilennummer und dem Buchstaben ‚C‘, gefolgt von der Spaltennummer, bezeichnet. z. B. wird B4 in der R1C1-Schreibweise mit R4C2 bezeichnet. In VBA benutzen Sie das Cells-Objekt, um die R1C1-Schreibweise zu verwenden:

' Auf die Zelle R[6]C[4], d.h. D6 verweisen
Cells(6, 4) = "D6"

Bereich von Zellen

A1-Schreibweise

Um auf mehr als eine Zelle zu verweisen, verwenden Sie ein „:“ zwischen der Adresse der Anfangszelle und der Adresse der letzten Zelle. Im Folgenden wird auf alle Zellen von A1 bis D10 verwiesen:

Range("A1:D10")

R1C1-Schreibweise

Um auf mehr als eine Zelle zu verweisen, verwenden Sie ein „,“ zwischen der Adresse der Startzelle und der letzten Zelle. Im Folgenden wird auf alle Zellen von A1 bis D10 verwiesen:

Range(Cells(1, 1), Cells(10, 4))

In Zellen schreiben

Um Werte in eine Zelle oder eine zusammenhängende Gruppe von Zellen zu schreiben, verweisen Sie einfach auf den Bereich, setzen Sie das Zeichen „=“ und schreiben Sie dann den zu speichernden Wert:

' F6 in der Zelle mit der Adresse F6 speichern
Range("F6") = "F6"

' E6 in der Zelle mit der Adresse R[6]C[5] speichern, d.h. E6
Cells(6, 5) = "E6"

' A1:D10 in den Bereich A1:D10 speichern
Range("A1:D10") = "A1:D10"
' oder
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

Aus Zellen herauslesen

Um Werte aus Zellen herauszulesen, verweisen Sie einfach auf die Variable, in der die Werte gespeichert werden sollen, setzen das Zeichen „=“ und verweisen dann auf den zu lesenden Bereich:

Dim Wert1
Dim Wert2

' Aus Zelle F6 lesen
Wert1 = Range("F6")

' Aus Zelle E6 lesen
Wert2 = Cells(6, 5)

MsgBox Wert1
Msgbox Wert2

Hinweis: Um Werte aus einem Bereich von Zellen zu speichern, müssen Sie ein Array anstelle einer einfachen Variablen verwenden.

Nicht benachbarte Zellen

Um sich auf nicht benachbarte Zellen zu beziehen, verwenden Sie ein Komma zwischen den Zellenadressen:

' 10 in den Zellen A1, A3 und A5 speichern
Range("A1,A3,A5") = 10


' 10 in den Zellen A1:A3 und D1:D3 speichern
Range("A1:A3, D1:D3") = 10

Schnittpunkt von Zellen

Um auf nicht benachbarte Zellen zu verweisen, verwenden Sie ein Leerzeichen zwischen den Zellenadressen:

' 'spalte D' in D1:D10 speichern
' die im Schnittbereich von A1:D10 und D1:F10 liegen
Range("A1:D10 D1:G10") = "Spalte D"

Abstand von einer Zelle oder einem Bereich

Mit der Funktion Offset können Sie die Referenzierung von einem bestimmten Bereich (Zelle oder Zellengruppe) um die angegebene Anzahl_der_Zeilen und Anzahl_der_Spalten verschieben.

Offset-Syntax

Range.Offset(anzahl_der_zeilen, anzahl_der_spalten)

Abstand von einer Zelle

' Abstand von einer Zelle A1
' Auf die Zelle selbst verweisen
' Um 0 Zeilen und 0 Spalten verschieben
Range("A1").Offset(0, 0) = "A1"

' Um 1 Zeile und 0 Spalte verschieben
Range("A1").Offset(1, 0) = "A2"

' Um 0 Zeilen und 1 Spalte verschieben
Range("A1").Offset(0, 1) = "B1"

' Um 1 Zeile und 1 Spalte verschieben
Range("A1").Offset(1, 1) = "B2"

' Um 10 Zeilen und 5 Spalten verschieben
Range("A1").Offset(10, 5) = "F11"

Abstand von einem Bereich

' Die Referenz zum Bereich A1:D4 um 4 Zeilen und 4 Spalten verschieben
' Die neue Referenz ist E5:H8
Range("A1:D4").Offset(4,4) = "E5:H8"

Referenz auf einen Bereich setzen

So weisen Sie einer Bereichsvariablen einen Bereich zu: Deklarieren Sie eine Variable vom Typ Range und verwenden Sie dann den Befehl Set, um sie auf einen Bereich zu setzen. Bitte beachten Sie, dass Sie den SET-Befehl verwenden müssen, da RANGE ein Objekt ist:

' Eine Range-Variable deklarieren
Dim meinBereich as Range

' Setzen Sie die Variable auf den Bereich A1:D4
Set meinBereich = Range("A1:D4")

' $A$1:$D$4 drucken
MsgBox meinBereich.Address

Größe eines Bereichs ändern

Die Resize-Methode des Range-Objekts ändert die Dimension des Referenzbereichs:

Dim meinBereich As Range

' Bereich zum Ändern der Größe
Set meinBereich = Range("A1:F4")

' Druckt $A$1:$E$10
Debug.Print meinBereich.Resize(10, 5).Address

Die obere linke Zelle des Bereichs mit geänderter Größe ist die gleiche wie die obere linke Zelle des ursprünglichen Bereichs

Resize-Syntax

Range.Resize(Anzahl_der_Zeilen, Anzahl_der_Spalten)

OFFSET vs. Resize

Bei Offset werden die Dimensionen des Bereichs nicht geändert, sondern um die angegebene Anzahl von Zeilen und Spalten verschoben. Bei Resize wird die Position des ursprünglichen Bereichs nicht verändert, sondern die Dimensionen werden auf die angegebene Anzahl von Zeilen und Spalten geändert.

Alle Zellen im Blatt

Das Cells-Objekt verweist auf alle Zellen des Blattes (1048576 Zeilen und 16384 Spalten).

' Inhalte aller Zellen in Worksheets löschen
Cells.Clear

UsedRange

Die Eigenschaft UsedRange gibt Ihnen den rechteckigen Bereich von der oberen linken verwendeten Zelle bis zur rechten unteren verwendeten Zelle des aktiven Blattes.

Dim ws As Worksheet
Set ws = ActiveSheet

' $B$2:$L$14, wenn L2 die erste Zelle mit einem beliebigen Wert ist 
' und L14 die letzte Zelle mit einem beliebigen Wert auf dem
' aktuellen Blatt
Debug.Print ws.UsedRange.Address

CurrentRegion

Die Eigenschaft UsedRange gibt Ihnen den rechteckigen Bereich von der verwendeten Zelle oben links bis zur verwendeten Zelle unten rechts auf dem aktuellen Blatt an.

Dim meinBereich As Range

Set meinBereich = Range("D4:F6")

' Druckt $B$2:$L$14
' Wenn es einen gefüllten Pfad von D4:F16 nach B2 UND L14 gibt
Debug.Print meinBereich.CurrentRegion.Address

' Sie können sich auch auf eine einzelne Startzelle beziehen

Set meinBereich = Range("D4") ' Druckt $B$2:$L$14

Bereichseigenschaften

Sie können die Adresse, die Zeilen- bzw. Spaltennummer einer Zelle und die Anzahl der Zeilen/Spalten in einem Bereich wie unten angegeben erhalten:

Dim meinBereich As Range

Set meinBereich = Range("A1:F10")

' Druckt $A$1:$F$10
Debug.Print meinBereich.Address

Set meinBereich = Range("F10")

' Druckt 10 für Zeile 10
Debug.Print meinBereich.Row

' Druckt 6 für Spalte F
Debug.Print meinBereich.Column

Set meinBereich = Range("E1:F5")
' Druckt 5 für die Anzahl der Zeilen im Bereich
Debug.Print meinBereich.Rows.Count

' Druckt 2 für die Anzahl der Spalten im Bereich
Debug.Print meinBereich.Columns.Count

Letzte Zelle im Blatt

Sie können die Eigenschaften Rows.Count und Columns.Count mit dem Cells-Objekt verwenden, um die letzte Zelle auf dem Blatt zu erhalten:

' Druckt die letzte Zeilennummer
' Druckt 1048576
Debug.Print "Zeilen im Blatt: " & Rows.Count

' Druckt die letzte Spaltennummer
' Druckt 16384
Debug.Print "Spalten im Blatt: " & Columns.Count

' Die Adresse der letzten Zelle drucken
' Druckt $XFD$1048576
Debug.Print "Adresse der letzten Zelle in der Tabelle: " & Cells(Rows.Count, Columns.Count)

Letzte verwendete Zeilennummer in einer Spalte

Mit der Eigenschaft END wird die letzte Zelle im Bereich angezeigt, und mit End(xlUp) wird die erste verwendete Zelle ab dieser Zelle angezeigt.

Dim letzteZeile As Long

letzteZeile = Cells(Rows.Count, "A").End(xlUp).Row

Letzte verwendete Spaltennummer in einer Zeile

Dim letzteSpalte As Long

letzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column

Mit der Eigenschaft END gelangen Sie zur letzten Zelle im Bereich und mit End(xlToLeft) zur ersten verwendeten Zelle links von dieser Zelle. Sie können auch die Eigenschaften xlDown und xlToRight verwenden, um zu den ersten unten oder rechts von der aktuellen Zelle verwendeten Zellen zu navigieren.

Eigenschaften von Zellen

Allgemeine Eigenschaften

Der folgende Code zeigt häufig verwendete Zelleneigenschaften an

Dim Zelle As Range
Set Zelle = Range("A1")

Zelle.Activate
Debug.Print Zelle.Address
' $A$1 drucken

Debug.Print Zelle.Value
' Druckt 456
' Adresse

Debug.Print Zelle.Formula
' Druckt =SUM(C2:C3)

' Kommentar
Debug.Print Zelle.Comment.Text

' Stil
Debug.Print Zelle.Style

' Zellenformat
Debug.Print Zelle.DisplayFormat.NumberFormat

Zellenschriftart

Das Objekt Cell.Font enthält die Eigenschaften der Zellenschriftart:

Dim Zelle As Range

Set Zelle = Range("A1")

' Normal, kursiv, fett und fett kursiv
Zelle.Font.FontStyle = "Bold Italic"
' Genauso wie
Zelle.Font.Bold = True
Zelle.Font.Italic = True

' Schriftart auf Courier setzen
Zelle.Font.FontStyle = "Courier"

' Schriftfarbe einstellen
Zelle.Font.Color = vbBlue
' oder
Zelle.Font.Color = RGB(255, 0, 0)

' Schriftgröße einstellen
Zelle.Font.Size = 20

Kopieren und Einfügen

Alles einfügen

Bereiche/Zellen können von einem Ort zum anderen kopiert und eingefügt werden. Der folgende Code kopiert alle Eigenschaften des Quellbereichs in den Zielbereich (entspricht STRG-C und STRG-V)

' Einfaches Kopieren
Range("A1:D20").Copy 
Worksheets("Blatt2").Range("B10").Paste

'oder
' Kopieren vom aktuellen Blatt auf das Blatt mit dem Namen 'Blatt2'
Range("A1:D20").Copy destination:=Worksheets("Blatt2").Range("B10")

Inhalte einfügen (PasteSpecial)

Ausgewählte Eigenschaften des Quellbereichs können mit der Option PASTESPECIAL in den Zielbereich kopiert werden:

' Den Bereich nur als Werte einfügen
Range("A1:D20").Copy
Worksheets("Blatt2").Range("B10").PasteSpecial Paste:=xlPasteValues

Hier sind die möglichen Einfügeoptionen:

' Tyoen von PasteSpecial
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

AutoFit Inhalt

Die Größe der Zeilen und Spalten kann mit dem folgenden Code an den Inhalt angepasst werden:

' Die Größe der Zeilen 1 bis 5 ändern, um sie an den Inhalt anzupassen 
Rows("1:5").AutoFit

' Die Größe der Spalten A bis B ändern, um sie an den Inhalt anzupassen 
Columns("A:B").AutoFit

Weitere Bereichsbeispiele

Es wird empfohlen, den Makrorekorder zu verwenden, während Sie die gewünschte Aktion über die grafische Benutzeroberfläche ausführen. Er wird Ihnen helfen, die verschiedenen verfügbaren Optionen und ihre Verwendung zu verstehen.

For Each

Es ist einfacher, einen Bereich mit dem For Each-Konstrukt, wie unten gezeigt, zu durchlaufen:

For Each cell In Range("A1:B100")
    ' Etwas mit der Zelle tun
Next cell

Bei jeder Iteration der Schleife wird eine Zelle des Bereichs der Variablen cell zugewiesen und die Anweisungen in der For-Schleife werden an dieser Zelle ausgeführt. Die Schleife wird beendet, wenn alle Zellen abgearbeitet sind.

Sortieren

Sort ist eine Methode des Range-Objekts. Sie können einen Bereich sortieren, indem Sie Optionen für die Sortierung in Range.Sort angeben. Der folgende Code sortiert die Spalten A:C anhand des Schlüssels in Zelle C2. Die Sortierreihenfolge kann xlAscending oder xlDescending sein. Header:= xlYes sollte verwendet werden, wenn die erste Zeile die Kopfzeile ist.

   Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlYes

Suchen

Find ist ebenfalls eine Methode des Range-Objekts. Sie findet die erste Zelle, deren Inhalt den Suchkriterien entspricht, und gibt die Zelle als Range-Objekt zurück. Sie gibt nichts zurück, wenn es keine Übereinstimmung gibt. Verwenden Sie die Methode FindNext (oder FindPrevious), um das nächste oder vorherige Vorkommen zu finden. Der folgende Code ändert die Schriftart in „Arial Black“ für alle Zellen im Bereich, die mit „John“ beginnen:

For Each c In Range("A1:A100")
    If c Like "John*" Then
        c.Font.Name = "Arial Schwarz"
    End If
Next c

Der folgende Code ersetzt alle Vorkommen von „Zum Testen“ durch „Bestanden“ in dem angegebenen Bereich:

With Range("a1:a500")
    Set c = .Find("Zum Testen", LookIn:=xlValues)
    If Not c Is Nothing Then
        ersteAdresse = c.Address
        Do
            c.Value = "Bestanden"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With

Es ist wichtig zu beachten, dass Sie einen Bereich angeben müssen, um FindNext zu verwenden. Außerdem müssen Sie eine Abbruchbedingung angeben, da die Schleife sonst endlos ausgeführt wird. Normalerweise wird die Adresse der ersten gefundenen Zelle in einer Variablen gespeichert und die Schleife wird angehalten, wenn diese Zelle wieder erreicht wird. Sie müssen auch den Fall prüfen, dass nichts gefunden wird, um die Schleife anzuhalten.

Bereichsadresse

Verwenden Sie Range.Address, um die Adresse in A1 Style zu erhalten

MsgBox Range("A1:D10").Address
' oder
Debug.Print Range("A1:D10").Address

Verwenden Sie xlReferenceStyle (Standard ist xlA1), um die Adresse im R1C1-Stil zu erhalten

MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
' oder
Debug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) 

Dies ist nützlich, wenn Sie mit in Variablen gespeicherten Bereichen arbeiten und nur bestimmte Adressen verarbeiten möchten.

Bereich zu Array

Es ist schneller und einfacher, einen Bereich in ein Array zu übertragen und dann die Werte zu verarbeiten. Sie sollten das Array als Variant deklarieren, um die Berechnung der Größe zu vermeiden, die erforderlich ist, um den Bereich in das Array zu übertragen. Die Dimensionen des Arrays werden so festgelegt, dass sie der Anzahl der Werte im Bereich entsprechen.

Dim DirArray As Variant
' Die Bereichswerte im Array speichern

DirArray = Range("a1:a5").Value

' Schleife zur Verarbeitung der Werte
For Each c In DirArray
    Debug.Print c
Next c

Array zu Range

Nach der Verarbeitung können Sie das Array  in einen Bereich zurückschreiben. Um das Array im obigen Beispiel in einen Bereich zu schreiben, müssen Sie einen Bereich angeben, dessen Größe der Anzahl der Elemente im Array entspricht. Verwenden Sie den folgenden Code, um das Array in den Bereich D1:D5 zu schreiben:

Range("D1:D5").Value = DirArray 

Range("D1:H1").Value = Application.Transpose(DirArray)

Bitte beachten Sie, dass Sie das Array transponieren müssen, wenn Sie es in eine Zeile schreiben.

Bereichssumme

Bereich_Summe = Application.WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print Bereich_Summe

Sie können viele in Excel verfügbare Funktionen in Ihrem VBA-Code verwenden, indem Sie Application.WorkSheetFunction. vor dem Funktionsnamen angeben, wie im obigen Beispiel.

Zellen im Bereich zählen

' Anzahl der Zellen mit Zahlen im Bereich bestimmen
AnzahlZellen = Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print AnzahlZellen

' Anzahl der nicht leeren Zellen im Bereich zählen
AnzahlNichtLeereZellen = Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print AnzahlNichtLeereZellen

Geschrieben von: Vinamra Chandra