Sometimes, you find that your file size is very huge even though data contained in not much. The reason for this is that there are unused formatting beyond your data range. Either you can clear off your rows and columns beyond data range but if this is frequent, you can use a macro to do this. You can perform following manual steps to do this without a macro.
Go to last cell in column A containing data. Select next row > CTRL+SHIFT+Down Arrow to select till last row of the sheet > Home tab > Clear > Clear All
Go to last cell in first row containing data. Select next column > CTRL+SHIFT+Right Arrow to select till last column of the sheet > Home tab > Clear > Clear All
Rather than manual, you can also use below macro to do this if this is needed frequently. This macro will also give you a chance to create a copy of your workbook so that you will have a backup in place. This will append _Timestamp at the end of the file to create backup file.
1. Open a workbook and ALT+F11 (I would recommend that you should keep this macro in your Personal.xlsb)
2. Locate your Workbook name in Project Explorer Window
3. Right click on your workbook name > Insert > Module
4. Copy paste the Macro code given
5. Go back to your Workbook and ALT+F8 to display Macro Window
6. Run your Macro from here
7. Delete you Macro if the Macro was needed to be run only once.
8. Otherwise save your file as .xlsm if you intend to reuse Macro again.
The sample file can be downloaded from Clear_Unused
Sub ClearUnused()
Dim LastCell As Range
Dim Answer
Dim LastRow As Long, LastColumn As Long, LastDotPosition As Long
Dim FullFileName As String, FileName As String, TargetFileName As String
'If no data, exit the macro
If WorksheetFunction.CountA(Cells) = 0 Then Exit Sub
Application.ScreenUpdating = False
Answer = MsgBox("Do you want to create a copy of this Workbook?", vbQuestion + vbYesNoCancel)
If Answer = vbCancel Then
GoTo ExitSub
End If
If Answer = vbYes Then
FullFileName = ActiveWorkbook.FullName
If FullFileName = "" Then
MsgBox "Save this file and then run the macro"
GoTo ExitSub
End If
'Insert Timestamp with an underscore at the end to create a new file name for backup copy
LastDotPosition = InStrRev(FullFileName, ".")
TargetFileName = WorksheetFunction.Replace(FullFileName, LastDotPosition, 0, "_" & Format(Now(), "yyyymmddhhmmss"))
ActiveWorkbook.SaveCopyAs TargetFileName
End If
'Now find lastrow and column which have values. Other tricks to locate won't work here
'as they will stop at the format also.
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Application.ScreenUpdating = False
'Clear Rows first
Rows(LastRow + 1 & ":" & Rows.Count).Clear
'Clear Columns
Columns(Split(Cells(1, LastColumn + 1).Address, "$")(1) & ":" & Split(Cells(1, Columns.Count).Address, "$")(1)).Clear
ActiveWorkbook.Save
ExitSub:
Application.ScreenUpdating = True
End Sub