VBA-Programmierung in Microsoft Excel

Alle Formate jenseits der letzten benutzten Zelle löschen

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

    

Beiträge aus dem Excel-Forum zu den Themen Format und Bereich