Mega Code Archive

 

Categories / VisualBasic Script / Access

 

Remove extraneous rows and columns and reset the last cell

Sub DeleteUnusedFormats()          Dim lLastRow As Long, lLastColumn As Long          Dim lRealLastRow As Long, lRealLastColumn As Long          With Range("A1").SpecialCells(xlCellTypeLastCell)              lLastRow = .Row              lLastColumn = .Column          End With          lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows, xlPrevious).Row          lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,, _                    xlByColumns, xlPrevious).Column          If lRealLastRow < lLastRow Then              Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete          End If          If lRealLastColumn < lLastColumn Then              Range(Cells(1, lRealLastColumn + 1), _                   Cells(1, lLastColumn)).EntireColumn.Delete          End If          ActiveSheet.UsedRange    'Resets LastCell      End Sub