| A | B | C | D | E | F | G | H | |
| 1 | ||||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 |
| A | B | C | D | E | F | G | |
| 1 | |||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 |
Sub Beispiel()
Dim LRow As Long
On Error Resume Next
'Finde Zeile
LRow = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False).Row
LRow = Application.Max(LRow, Cells.Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row)
If LRow = 0 Then LRow = 1
On Error GoTo 0
'die letzte = LRow
'die nächste freie = LRow + 1
'ist die Tabelle komplett leer, ist LRow = 1
MsgBox "Die letzte belegte Zeile = " & LRow & vbCr & "Die nächste freie Zeile = " & LRow + 1
End Sub
Function FindLetzte(mySH As Worksheet) As Range
Dim LRow As Long, LCol As Long
Dim A As Long
With mySH.UsedRange
On Error Resume Next
'Finde Zeile
LRow = .Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False).Row
LRow = Application.Max(LRow, .Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row)
If LRow = 0 Then LRow = 1
'Finde Spalte
For A = .Columns(.Columns.Count).Column To .Columns(1).Column Step -1
LCol = mySH.Columns(A).Find("*", , xlValues, xlWhole, xlByRows, xlPrevious).Row
LCol = Application.Max(LCol, mySH.Columns(A).Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row)
If LCol > 1 Then: LCol = A: Exit For
Next A
On Error GoTo 0
If LRow = 0 Then LRow = 1
If LCol = 0 Then LCol = 1
End With
Set FindLetzte = mySH.Cells(LRow, LCol)
End Function
Sub Beispiel()
MsgBox FindLetzte(ActiveSheet).Address
End Sub
| A | B | |
| 1 | 6 | |
| 2 | ||
| 3 | ||
| 4 | 5 | |
| 5 | ||
| 6 | 6 | |
| 7 | ||
| 8 |
| Formeln der Tabelle | ||||||
| ||||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||||
| Matrix verstehen |
Sub Letzte_Zelle_färben()
Dim Wertebereich As Range, LetzterBereich As Range, LetzteZelle As Range
With ActiveSheet
Set Wertebereiche = _
Application.Intersect(.UsedRange, .Cells.SpecialCells(xlCellTypeConstants))
Set LetzterBereich = _
.Range(Wertebereiche.Areas(Wertebereiche.Areas.Count).Address)
Set LetzteZelle = _
.Range(Split(LetzterBereich.Address, ":")(UBound(Split(LetzterBereich.Address, ":"))))
End With
LetzteZelle.Interior.ColorIndex = 33
End Sub
