' **********************************************************************
' Modul: Modul3 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Sub test()
'Aktives Tabellenblatt, Spalte 1
MsgBox lastCell(ActiveSheet, 1)
End Sub
Function lastCell(ByRef sh As Worksheet, ByVal ColOrRow As Long, Optional ByVal LastRow As Boolean = True) As Long
On Error GoTo ErrExit
With sh
If ColOrRow < 1 Or (LastRow And ColOrRow > .Rows.Count) Or _
(Not LastRow And ColOrRow > .Columns.Count) Then Exit Function
.Parent.CustomViews.Add "##TempView##", False, True
If LastRow Then
.Rows.Hidden = False
If .AutoFilterMode Then If .FilterMode Then .ShowAllData
If .Cells(.Rows.Count, ColOrRow) <> "" Then
lastCell = .Rows.Count
Else
lastCell = .Cells(.Rows.Count, ColOrRow).End(xlUp).Row
End If
Else
.Columns.Hidden = False
If .Cells(ColOrRow, .Columns.Count) <> "" Then
lastCell = .Columns.Count
Else
lastCell = .Cells(ColOrRow, .Columns.Count).End(xlToLeft).Column
End If
End If
ErrExit:
On Error Resume Next
With .Parent.CustomViews("##TempView##")
.Show
.Delete
End With
On Error GoTo 0
End With
End Function
Sub PruefenWerte()
Dim intCounter As Integer
intCounter = 1
Do Until Sheets("Daten").Cells(intCounter, 1).Value = ""
intCounter = intCounter + 1
If intCounter > 100000 Then
Exit Do
End If
Loop
MsgBox intCounter
End Sub
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(.Rows(lngTmp), .Rows(lngLastRow))) Then _
lngFirstRow = lngTmp: blnFound = True
If Not blnFound And .CountA(wks.Range(.Rows(lngFirstRow), .Rows(lngTmp))) Then _
lngLastRow = lngTmp
LastRow wks, lngFirstRow, lngLastRow
End If
End With
LastRow = lngFirstRow
End Function