AW: Letzte Zeile von ausgeblendetem Blatt
16.12.2011 16:19:15
ausgeblendetem
Hallo,
so geht das:
Function LastRow( _
wks As Worksheet, _
Optional lngFirstRow As Long, _
Optional lngLastRow As Long) _
As Long
'letzte Zeile mit Inhalt
Dim lngTmp As Long, blnFound As Boolean
With Application
If .CountA(wks.Rows(wks.Rows.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 .CountA(wks.Range(wks.Rows(lngTmp), wks.Rows(lngLastRow))) Then _
lngFirstRow = lngTmp: blnFound = True
If Not blnFound And .CountA(wks.Range(wks.Rows(lngFirstRow), wks.Rows(lngTmp))) Then _
lngLastRow = lngTmp
LastRow wks, lngFirstRow, lngLastRow
End If
End With
LastRow = lngFirstRow
End Function
Sub tt()
MsgBox LastRow(Sheets(1))
End Sub
Gruß
Rudi