Gruppe
Format
Problem
Über den ShortCut STRG+q sollen alle Formate in den Spalten rechts und den Zeilen unterhalb der letzten benutzten Zelle gelöscht und die lezte benutzte Zelle ausgewählt werden.
StandardModule: Modul1
Sub ClearFormats()
Dim rng As Range
Set rng = RealLastCell(ActiveSheet)
Range(rng.Offset(0, 1), Cells(1, Columns.Count)).EntireColumn.Delete
Rows(CStr(rng.Row + 1) & ":" & Rows.Count).Delete
rng.Select
End Sub
Function RealLastCell(TheSheet As Worksheet) As Range
Dim ExcelLastCell As Range
Dim Row%, Col%, LastRowWithData%, LastColWithData%
Application.ScreenUpdating = False
Set ExcelLastCell = TheSheet.Cells.SpecialCells(xlLastCell)
LastRowWithData = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Application.CountA(TheSheet.Rows(Row)) = 0 And Row <> 1
Row = Row - 1
Loop
LastRowWithData = Row
LastColWithData = ExcelLastCell.Column
Col = ExcelLastCell.Column
Do While Application.CountA(TheSheet.Columns(Col)) = 0 And Col <> 1
Col = Col - 1
Loop
LastColWithData = Col
Set RealLastCell = TheSheet.Cells(Row, Col)
End Function