Excel VBA – delete all blank columns without any content

This is a function to identify when the column(s) doesn’t have any content in each cell, then delete that entire column(s).  It is a amazing function to clean up your spreadsheets before modifying your data.

Function DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Columns.Count > 1 Then
Set rng = Selection
Else
Set rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
End If
ColCnt = 0
For Col = rng.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Columns(Col).EntireColumn) = 0 Then
rng.Columns(Col).EntireColumn.Delete
ColCnt = ColCnt + 1
End If
Next Col

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function

Excel VBA – delete all blank rows without any content

This is a function to identify when the row(s) doesn’t have any content in each cell, then delete that entire row(s).  It is a amazing function to clean up your spreadsheets before modifying your data.

Function DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(Rw).EntireRow) = 0 Then
rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function