letzte Zelle mit Inhalt
09.12.2011 12:07:39
Rudi
Hallo,
per UDF:
Function LastRow_oBlanks( _
wks As Worksheet, _
Optional lngFirstRow As Long, _
Optional lngLastRow As Long) _
As Long
'letzte Zeile mit Inhalt, Blanks durch Formeln gelten als leer
Dim lngTmp As Long, blnFound As Boolean
With Application
If .CountBlank(wks.Rows(wks.Rows.Count)) wks.Rows(wks.Rows.Count).Cells.Count Then
LastRow = wks.Rows.Count: Exit Function
End If
If .CountA(wks.Cells) = 0 Then
LastRow = 0: Exit Function
End If
If lngFirstRow = 0 Then lngFirstRow = 1
If lngLastRow = 0 Then lngLastRow = wks.Rows.Count
lngTmp = (lngFirstRow + lngLastRow) / 2
If lngLastRow > lngFirstRow + 1 Then
If .CountBlank(wks.Range(.Rows(lngTmp), .Rows(lngLastRow))) _
wks.Range(.Rows(lngTmp), .Rows(lngLastRow)).Cells.Count Then
lngFirstRow = lngTmp: blnFound = True
End If
If Not blnFound And .CountBlank(wks.Range(.Rows(lngFirstRow), .Rows(lngTmp))) _
wks.Range(.Rows(lngFirstRow), .Rows(lngTmp)).Cells.Count Then
lngLastRow = lngTmp
End If
LastRow wks, lngFirstRow, lngLastRow
End If
End With
LastRow = lngFirstRow
End Function
Gruß
Rudi