Tipp 0342
|
Letzte Zeile, Spalte, Zelle mit Daten ermitteln
|
|
|
Autor/Einsender: Datum: |
|
Angie 07.01.2006 (Update) |
|
Entwicklungsumgebung: |
|
Excel 2000 |
|
|
In Excel gibt es keine integrierte Methode, mit der man die letzte Zeile, Spalte oder Zelle, die Daten enthält, zuverlässig ermitteln kann.
Die SpecialCells-Methode in Verbindung mit der Konstante xlCellTypeLastCell berücksichtigt leider nicht
nur Daten, sondern auch ggf. vorhandene Formatierungen der Zellen. Dies trifft auch auf die UsedRange-Eigenschaft zu.
|
Wenn zum Beispiel in einem zunächst leeren Tabellenblatt in der Zelle C3 ein Datum eingegeben und anschließend mit der
Taste [Entf] gelöscht wird, wird bei Ausführung des Dialogs GeheZu... mit der Option 'Letzte Zelle' (Menü / Bearbeiten / GeheZu... /
Inhalte...) die Zelle C3 ausgewählt (VBA: Selection.SpecialCells(xlCellTypeLastCell).Select),
da diese Zelle nach wie vor als Datum formatiert ist, obwohl der Inhalt der Zelle gelöscht wurde. Manuell könnte man über das
Menü / Bearbeiten / Löschen / Alles sowohl den Inhalt als auch ggf. vorhandene Formatierungen löschen. Trotzdem würde
zunächst als 'Letzte Zelle' die Zelle C3 ausgewählt werden. Nur ein anschließendes Speichern der Arbeitsmappe liefert dann das "richtige" Ergebnis.
|
|
Verwendeter Bereich mit Daten ermitteln |
|
Wenn in einem Tabellenblatt der tatsächlich verwendete Bereich, also inkl. formatierter Zellen, die evtl. keine Daten enthalten, ermittelt werden soll, kann die
SpecialCells-Methode oder die UsedRange-Eigenschaft verwendet werden. Soll jedoch nur der verwendete Bereich,
der Daten (Konstanten und Formeln) enthält, ermittelt werden, so könnte die folgende Funktion ein Lösungsansatz sein.
|
|
|
Public Function GetDataRange(ByRef objWks As Worksheet) As Range
Dim objUsedRange As Range
Dim objAreaRange As Range
Dim nFirstColA As Integer
Dim nFirstRowA As Long
Dim nLastColA As Integer
Dim nLastRowA As Long
Dim nFirstCol As Integer
Dim nFirstRow As Long
Dim nLastCol As Integer
Dim nLastRow As Long
On Error Resume Next
If Application.WorksheetFunction.CountA(objWks.Cells) > 0 Then
Set objUsedRange = Union( _
objWks.UsedRange.SpecialCells(xlCellTypeConstants), _
objWks.UsedRange.SpecialCells(xlCellTypeFormulas))
If objUsedRange Is Nothing Then
Set objUsedRange = _
objWks.UsedRange.SpecialCells(xlCellTypeConstants)
End If
If objUsedRange Is Nothing Then
Set objUsedRange = _
objWks.UsedRange.SpecialCells(xlCellTypeFormulas)
End If
If Not objUsedRange Is Nothing Then
nFirstCol = objWks.Columns.Count
nFirstRow = objWks.Rows.Count
For Each objAreaRange In objUsedRange.Areas
nFirstColA = objAreaRange.Column
nFirstRowA = objAreaRange.Row
nLastColA = nFirstColA + objAreaRange.Columns.Count - 1
nLastRowA = nFirstRowA + objAreaRange.Rows.Count - 1
If nFirstColA < nFirstCol Then nFirstCol = nFirstColA
If nFirstRowA < nFirstRow Then nFirstRow = nFirstRowA
If nLastRowA > nLastRow Then nLastRow = nLastRowA
If nLastColA > nLastCol Then nLastCol = nLastColA
Next objAreaRange
Set GetDataRange = _
objWks.Range(objWks.Cells(nFirstRow, nFirstCol), _
objWks.Cells(nLastRow, nLastCol))
End If
On Error GoTo 0
End If
End Function
|
|
|
Erste und letzte Spalte und Zeile mit Daten ermitteln |
|
Mit der obigen Funktion GetDataRange() kann nicht nur der in einem Tabellenblatt verwendete Bereich mit Daten ermittelt werden,
sondern auch die erste und letzte verwendete Spalte und Zeile mit Daten.
|
|
|
Sub Demo_GetFirstAndLastColAndRow()
Dim objWks As Worksheet
Dim objDataRange As Range
Dim nFirstCol As Integer
Dim nFirstRow As Long
Dim nLastCol As Integer
Dim nLastRow As Long
Dim strMsg As String
Set objWks = ActiveWorkbook.Worksheets(1)
Set objDataRange = GetDataRange(objWks)
If Not objDataRange Is Nothing Then
nFirstCol = objDataRange.Column
nFirstRow = objDataRange.Row
nLastCol = objDataRange.Column + objDataRange.Columns.Count - 1
nLastRow = objDataRange.Row + objDataRange.Rows.Count - 1
strMsg = "Tabellenblatt-Name: " & objWks.Name & vbCr & vbCr
strMsg = strMsg & "Erste Spalte: " & vbTab & nFirstCol & vbCr
strMsg = strMsg & "Letzte Spalte: " & vbTab & nLastCol & vbCr
strMsg = strMsg & "Erste Zeile: " & vbTab & nFirstRow & vbCr
strMsg = strMsg & "Letzte Zeile: " & vbTab & nLastRow
Set objDataRange = Nothing
Else
strMsg = "Das Tabellenblatt '" & objWks.Name & _
"' enthält keine Daten!"
End If
MsgBox strMsg, vbInformation, "VB-fun-Demo - " & _
"Erste und letzte verwendete Spalte/Zeile mit Daten"
Set objWks = Nothing
End Sub
|
|
|
Erste und letzte Spalte und Zeile mit Daten ermitteln (Find-Methode) |
|
Alternativ kann aber auch die Find-Methode verwendet werden, um die letzte Spalte und Zeile mit Daten zu ermitteln.
|
In diesem Zusammenhang sei jedoch darauf hingewiesen, dass ab Excel 2000 (!) bei der Verwendung der Find-Methode in
Verbindung mit zusammengeführten (verbundenen) Zellen unter bestimmten Umständen der Laufzeitfehler '91' ausgelöst wird.
Der Laufzeitfehler wird ab Excel 2000 (!) beispielsweise dann ausgelöst, wenn im Tabellenblatt zwar Daten enthalten sind, diese
sich aber nur in zusammengeführten Zellen befinden.
|
Es ist also nicht zu empfehlen, die folgenden Beispiele zu verwenden, wenn im Tabellenblatt zusammengeführte Zellen enthalten sind.
|
Letzte S p a l t e mit Daten in Tabellenblatt
|
|
|
Sub FindLastColumnInWS()
Dim objWks As Worksheet
Dim nLastCol As Integer
Dim strMsg As String
Set objWks = ActiveWorkbook.Worksheets(1)
With objWks
If Application.WorksheetFunction.CountA(.Cells) > 0 Then
On Error Resume Next
nLastCol = .Cells.Find(What:="*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
If Err.Number = 0 Then
strMsg = "Letzte Spalte mit Daten: " & nLastCol
Else
strMsg = "Laufzeitfehler '" & CStr(Err.Number)
strMsg = strMsg & "':" & vbCr & Err.Description
End If
On Error GoTo 0
Else
strMsg = "Das Tabellenblatt enthält keine Daten!"
End If
End With
strMsg = "Tabellenblatt: " & objWks.Name & vbCr & vbCr & strMsg
MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
"Letzte Spalte mit Daten ermitteln (Find-Methode)"
Set objWks = Nothing
End Sub
|
|
|
Letzte Z e i l e mit Daten in Tabellenblatt
|
|
|
Sub FindLastRowInWS()
Dim objWks As Worksheet
Dim nLastRow As Long
Dim strMsg As String
Set objWks = ActiveWorkbook.Worksheets(1)
With objWks
If Application.WorksheetFunction.CountA(.Cells) > 0 Then
On Error Resume Next
nLastRow = .Cells.Find(What:="*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
If Err.Number = 0 Then
strMsg = "Letzte Zeile mit Daten: " & nLastRow
Else
strMsg = "Laufzeitfehler '" & CStr(Err.Number)
strMsg = strMsg & "':" & vbCr & Err.Description
End If
On Error GoTo 0
Else
strMsg = "Das Tabellenblatt enthält keine Daten!"
End If
End With
strMsg = "Tabellenblatt: " & objWks.Name & vbCr & vbCr & strMsg
MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
"Letzte Zeile mit Daten ermitteln (Find-Methode)"
Set objWks = Nothing
End Sub
|
|
|
Letzte Z e i l e mit Daten in angegebener Spalte ermitteln |
|
Die letzte verwendete Zeile in einer angegebenen Spalte ermittelt man unter anderem auch, um Daten an einen bestehenden Datenbestand
anfügen zu können. Sind in der angegebenen Spalte zusammengeführte Zellen enthalten, so wird unter Umständen jedoch ein Wert zurückgegeben,
der nicht geeignet ist, die nächsten "freie" Zeile zu ermitteln.
|
Wenn beispielsweise die Zellen A1 und B1 zusammengeführt wurden, ist der Zellbezug die Zelle A1, also die Zelle, die sich im
ursprünglichen Bereich in der oberen linken Ecke befindet. Sind nur in der Zelle A1 Daten enthalten, würde bei der Ermittlung der
letzten verwendeten Zeile in Spalte 2 zurückgegeben werden, dass keine Daten in der angegebenen Spalte vorhanden sind. Es ist jedoch
nicht möglich, der Zelle B1 einen Wert zuzuweisen!
|
|
|
Sub GetLastRowInColumn()
Dim objWks As Worksheet
Dim nCol As Integer
Dim nLastRow As Long
Dim strMsg As String
Set objWks = ActiveWorkbook.Worksheets(1)
nCol = 2
With objWks
If Application.WorksheetFunction.CountA( _
.Columns(nCol).EntireColumn) > 0 Then
If Len(.Cells(.Rows.Count, nCol).Value) = 0 Then
nLastRow = .Cells(.Rows.Count, nCol).End(xlUp).Row
Else
nLastRow = .Rows.Count
End If
strMsg = "Die letzte Zeile, die in Spalte " & nCol & _
" Daten enthält, ist die Zeile " & nLastRow
Else
strMsg = "Die Spalte " & nCol & " enthält keine Daten!"
End If
End With
strMsg = "Tabellen-Name: " & objWks.Name & vbCr & vbCr & strMsg
MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
"Letzte Z e i l e mit Daten in angegebener Spalte"
Set objWks = Nothing
End Sub
|
|
|
Letzte S p a l t e mit Daten in angegebener Zeile ermitteln |
|
Die letzte verwendete Spalte in einer angegebenen Zeile ermittelt man unter anderem auch, um Daten an einen bestehenden Datenbestand
anfügen zu können. Sind in der angegebenen Zeile zusammengeführte Zellen enthalten, so wird jedoch ein Wert zurückgegeben, der unter
Umständen nicht geeignet ist, die nächsten "freie" Spalte zu ermitteln.
|
Wurden beispielsweise die Zellen A1 und B1 zusammengeführt, ist der Zellbezug die Zelle A1, also die Zelle, die sich im ursprünglichen
Bereich in der oberen linken Ecke befindet. Sind nur in der Zelle A1 Daten enthalten, würde bei der Ermittlung der letzten verwendeten
Spalte in Zeile 1 die Spalte 1 zurückgegeben werden. Errechnet man nun, dass die nächste "freie" Spalte die Spalte 2 ist (Zelle B1),
so müsste hier eine zusätzliche Überprüfung stattfinden, da der Zelle B1 durch das Verbinden mit Zelle A1 kein Wert zugewiesen werden kann.
|
|
|
Sub GetLastColumnInRow()
Dim objWks As Worksheet
Dim nRow As Long
Dim nLastCol As Integer
Dim strMsg As String
Set objWks = ActiveWorkbook.Worksheets(1)
nRow = 2
With objWks
If Application.WorksheetFunction.CountA( _
.Rows(nRow).EntireRow) > 0 Then
If Len(.Cells(nRow, .Columns.Count).Value) = 0 Then
nLastCol = .Cells(nRow, .Columns.Count) _
.End(xlToLeft).Column
Else
nLastCol = .Columns.Count
End If
strMsg = "Die letzte Spalte, die in Zeile " & nRow & _
" Daten enthält, ist die Spalte " & nLastCol
Else
strMsg = "Die Zeile " & nRow & " enthält keine Daten!"
End If
End With
strMsg = "Tabellenblatt: " & objWks.Name & vbCr & vbCr & strMsg
MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
"Letzte S p a l t e mit Daten in angegebener Zeile"
Set objWks = Nothing
End Sub
|
|
|
Zusammengeführte/verbundene Zellen |
|
Wie schon mehrfach angesprochen, kann es zu Problemen kommen, wenn im angegebenen Bereich zusammengeführte Zellen enthalten sind.
Mit der MergeCells-Eigenschaft kann dies überprüft werden. Weitere Infos zum Thema können der Excel-VB(A)-Hilfe entnommen werden.
|
|
|
Sub Demo_MergeCells()
Dim varMergeCells As Variant
varMergeCells = Worksheets(1).UsedRange.MergeCells
If varMergeCells = False Then
MsgBox "Keine zusammengeführten Zellen im angegebenen " & _
"Bereich enthalten!"
ElseIf varMergeCells = True Then
MsgBox "Alle Zellen im angegebenen Bereich zusammengeführt!"
ElseIf IsNull(varMergeCells) Then
MsgBox "Es sind zusammengeführte Zellen im angegebenen " & _
"Bereich enthalten!"
Else
'Kann eigentlich nicht vorkommen!
End If
End Sub
|
|
|
|
Das Download-Beispiel ist so konzipiert, dass die oben beschriebenen "Einschränkungen" sowie der Laufzeitfehler ab Excel 2000 nachvollzogen werden können.
|
|