I assume that you are talking about column and rows after the last populated
data. I also assume that you want a macro solution. Here is some code that I
use (it is in an addin so I can use it on any open workbook)
Public Sub CompactAllSheets() 'Run Me...
On Error GoTo ErrorHandler
Dim wks As Worksheet
Dim blnIsProtected As Boolean
Dim wbk As Workbook
Application.ScreenUpdating = False
Set wbk = ActiveWorkbook
For Each wks In Worksheets
If wks.ProtectContents = True Then
blnIsProtected = True
wks.Unprotect "MyPassword" 'Worksheet password
Else
blnIsProtected = False
End If
Call CompactSheet(wks)
If blnIsProtected = True Then wks.Protect "MyPassword"
Next wks
Application.ScreenUpdating = True
If MsgBox("For the compact to complete the spreadsheet must be saved. "
& _
"Do you want to save now?", vbYesNo + vbInformation, "Save?") = vbYes
Then wbk.Save
End Sub
Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range
If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete
End Sub
Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer
If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function
