Jeder Excel-Anwender steht häufig vor der Aufgabe alle
Zellen vertikal bis Tabellenende ausgehend von der aktiven Zelle zu markieren
oder diesen Bereich mittels VBA bearbeiten zu wollen.
Hinweis: Mit Tabellenende ist nicht die letzte gefüllte Zelle im Arbeitsblatt gemeint, sondern die letzte Zeile einer Datenliste.
Hinweis: Mit Tabellenende ist nicht die letzte gefüllte Zelle im Arbeitsblatt gemeint, sondern die letzte Zeile einer Datenliste.
Die bekannte Tastenkombination [Umschalt] + [Strg] + [PfeilNachUnten] ist leider nicht immer hilfreich, da diese bei Leer-Zellen endet
und nicht zwangsläufig bei Tabellenende.
Die Tastenkombination [Strg]+[a] findet zuverlässig das
Tabellenende (insofern keine durchgehende Leerzeile sich in der Tabellenliste
sich befindet, wo von ich ausgehe).
Leider ist dann die gesamte Tabelle ausgewählt und nicht der
eigentliche gewünschter Bereich.
Daher schreiben wir uns schnelle die folgende VBA-Prozedur:
Public Function CurrentRegionDownward(Optional
StartupRange As Range _
, Optional SelectRange As Boolean = True) As Range
, Optional SelectRange As Boolean = True) As Range
'Copyright (r) by Jean Pierre Allain
Dim t As Range, r As Long
Dim t As Range, r As Long
'Wenn kein
StartupRange angegeben wurde, dann wird die Aktive Zelle verwendet
If StartupRange Is
Nothing Then Set StartupRange = ActiveCell
'Der
StartupRange wird auf eine Zelle reduziert
Set StartupRange =
Cells(StartupRange.Row, StartupRange.Column)
'Die Methode
CurrentRegion wählt den gesamten zusammenhängenden Bereich
'in einer Liste. Dieser wird in t gespeichert
'in einer Liste. Dieser wird in t gespeichert
Set t = StartupRange.CurrentRegion
'Die letzte Zeile aus t wird ausgelesen und in r gespeichert
r = t.Row +
t.Rows.Count - 1
'Nun wird der
Bereich von StartupRange bis zum Tabellenende gespeichert
Set StartupRange =
Range(StartupRange, Cells(r, StartupRange.Column))
'Wenn
SelectRange=True, dann wird der ermittelte Bereich
'markiert/ausgewählt/selektiert
'markiert/ausgewählt/selektiert
If SelectRange Then StartupRange.Select
'Die Funktion liefert als Rückgabewert den ermittelte Bereich
'als Range-Objekt zurück
'als Range-Objekt zurück
Set
CurrentRegionDownward = StartupRange
End Function
Um den zuvor beschriebenen Bereich zu markieren, muss
lediglich diese Funktionsprozedur ausgeführt werden.
Diese Funktionalität kann schnell und einfach verfügbar
gemacht werden, in dem sie in die Schnellzugriffsleiste oder in einem Menüband
zugeordnet wird.
Hierfür benötige wir asllerdings noch einen kleinen Trick,
den bei der Makrozuweisung listet Excel leider nur Sub-Prozeduren ohne
Übergabeparameter auf.
Daher tricksen wir Excel aus, in dem wir kurzfristig unsere
Prozedur in einer Sub-Prozedur umwandeln:
Public Sub CurrentRegionDownward()
‘Public Function
CurrentRegionDownward(Optional StartupRange As Range
_, Optional SelectRange
As Boolean = True) As Range
Nun lässt sich unsere Prozedur problemlos auf eine
Schaltflächensymbol in der Schnellzugriffsleiste oder ins Menüband zuweisen.
Anschließend bitte nicht vergessen, die Sub-Prozedur wieder in
eine Funktionsprozedur umzuwandeln:
‘Public Sub
CurrentRegionDownward()
Public Function CurrentRegionDownward(Optional
StartupRange As Range
_, Optional SelectRange As Boolean = True) As Range
Diese Lösung kann auch sehr einfach und effizient in der VBA-Programmierung
eingesetzt werden.
Beispiele:
CurrentRegionDownward(, False).FillDown
‘Kopiert den Wert in der aktiven Zelle
bis zum Tabellenende (Ohne den Zielbereich zu markieren)
CurrentRegionDownward(Range("A10")).FillDown
‘Kopiert den Wert in der Zelle A10 bis
zum Tabellenende (dabei wird der Zielbereich markiert)
Mit dieser kleinen Funktion wünsche ich alle eine Große Arbeitserleichterung.
Ich dachte es geht darum bis zur letzten verwendeten Zeile zu markieren?
AntwortenLöschenIch hab mir 2 Funktionen geschrieben die mir die letzte verwendete Zeile oder Spalte zurück geben. Egal ob Leerzellen dazwischen sind.
' -------------------------------------------------------------------
' Ermittelt die letzte verwendete Zeile einer Spalte
' wird keine Spalte angegeben so wird Spalte 1 = A verwendet
Function Get_Last_Row(Optional vSpalte As Variant = 1, Optional wsh As Worksheet) As Long
' WE 11-07-28
If wsh Is Nothing Then Set wsh = ActiveSheet
If IsNumeric(vSpalte) = True Then
Get_Last_Row = wsh.Cells(Columns(CInt(vSpalte)).Cells.Count, CInt(vSpalte)).End(xlUp).Row
Else
Get_Last_Row = wsh.Cells(Columns(vSpalte).Cells.Count, vSpalte).End(xlUp).Row
End If
End Function
' -------------------------------------------------------------------
' Ermittelt die letzte verwendete Spalte einer Zeile
' wird keine Zeile angegeben so wird Zeile 1 verwendet
Function Get_Last_Col(Optional lngZeile As Long = 1, Optional wsh As Worksheet) As Long
' WE 11-08-03
If wsh Is Nothing Then Set wsh = ActiveSheet
Get_Last_Col = wsh.Cells(lngZeile, Rows(lngZeile).Cells.Count).End(xlToLeft).Column
End Function
Vielen dank für den Hinweis, damit habe ich die Anforderung jetzt nochmal präzisiert.
AntwortenLöschenMit Tabellenende ist das Ende einer Tabelle gemeint und eine Tabelle kann man auch als Datenliste/Datenbank bezeichnen. Es ist eine Liste mit einen Zusammenhängenden gefüllten Bereich. Und das Ende dieser Liste gilt es heraus zu finden.