Excel doesn’t always update the range of cells you’ve used in worksheets when you modify them. This can result in a larger file size and have performance impacts, even after you clear cell contents. The easiest way to fix this is by deleting the blank rows and columns at the end of each worksheet. This article will show you how to delete blank rows and columns at the end of worksheets using VBA.

The code we’re going to be using in this article begins with parameter definitions and initialization of some of those variables. The rows under the Optimize Code section can help with the code performance. If you’re interested about speeding up your VBA macros, also see Improving VBA Macro Performance.

The code below loops through rows and columns inside the active worksheet, starting with the rows, from last to first. The code  looks at each row using the COUNTA function to check if there are any cells with values in them. If there are no cells with data in that range, the code combines these rows under the rngDelete variable of the Union function.

Finally, the code deletes the resulting rows combined in the rngDelete variable, and the same process is repeated for columns. After removing rows and columns, the Used Range is reset by the ActiveSheet.UsedRange command. The code finishes after taking optimization settings back to their defaults.

You need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

 

Delete blank rows and columns at the end of worksheets

Sub RemoveBlankRowsColumns()
'Remove blank rows and columns at the end of worksheets

 

‘Define variables
Dim rng As Range
Dim rngDelete As Range
Dim RowCount As Long, ColCount As Long
Dim RowDeleteCount As Long, ColDeleteCount As Long, DeleteCount As Long
Dim x As Long

 

‘Set variables
Set rng = ActiveSheet.UsedRange
RowCount = rng.Rows.Count
ColCount = rng.Columns.Count
DeleteCount = 0

 

‘Optimize Code
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

 

‘Loop through rows and detect blank one
For x = RowCount To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(x)) <> 0 Then
Exit For ‘if a non-blank row is found, finish searching
Else
If rngDelete Is Nothing Then Set rngDelete = rng.Rows(x)
Set rngDelete = Union(rngDelete, rng.Rows(x))
RowDeleteCount = RowDeleteCount + 1
End If
Next x

 

‘Delete rows
If Not rngDelete Is Nothing Then
rngDelete.EntireRow.Delete Shift:=xlUp
Set rngDelete = Nothing
End If

 

‘Loop through columns and detect blank one
For x = ColCount To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Columns(x)) <> 0 Then
Exit For ‘if a non-blank column is found, finish searching
Else
If rngDelete Is Nothing Then Set rngDelete = rng.Columns(x)
Set rngDelete = Union(rngDelete, rng.Columns(x))
ColDeleteCount = ColDeleteCount + 1
End If
Next x

 

‘Delete columns
If Not rngDelete Is Nothing Then
rngDelete.EntireColumn.Delete
End If

 

‘Refresh UsedRange
ActiveSheet.UsedRange

 

‘Prepare to exit
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

 

End Sub