» Delete rows and columns using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions With the macros below you can delete all empty rows or columns within a worksheet range.
You can also delete every n-th row or column. The macros will run faster if you add
Application.ScreenUpdating = False to the code.

Sub DeleteEmptyRows(DeleteRange As Range)
' Deletes all empty rows in DeleteRange
' Example: DeleteEmptyRows Selection
' Example: DeleteEmptyRows Range("A1:D100")
Dim rCount As Long, r As Long
    If DeleteRange Is Nothing Then Exit Sub
    If DeleteRange.Areas.Count > 1 Then Exit Sub
    With DeleteRange
        rCount = .Rows.Count
        For r = rCount To 1 Step -1
            If Application.CountA(.Rows(r)) = 0 Then 
                .Rows(r).EntireRow.Delete
            End If
        Next r
    End With
End Sub


Sub DeleteEmptyColumns(DeleteRange As Range)
' Deletes all empty columns in DeleteRange
' Example: DeleteEmptyColumns Selection
' Example: DeleteEmptyColumns Range("A1:Z1")
Dim cCount As Integer, c As Integer
    If DeleteRange Is Nothing Then Exit Sub
    If DeleteRange.Areas.Count > 1 Then Exit Sub
    With DeleteRange
        cCount = .Columns.Count
        For c = cCount To 1 Step -1
            If Application.CountA(.Columns(c)) = 0 Then 
                .Columns(c).EntireColumn.Delete
            End If
        Next c
    End With
End Sub


Sub DeleteEveryNthRow(DeleteRange As Range, N As Integer)
' Deletes every n-th row in DeleteRange
' Example: DeleteEveryNthRow Selection,2
' Example: DeleteEveryNthRow Range("A1:D100"),4
Dim rCount As Long, r As Long
    If DeleteRange Is Nothing Then Exit Sub
    If DeleteRange.Areas.Count > 1 Then Exit Sub
    If N < 2 Then Exit Sub
    With DeleteRange
        rCount = .Rows.Count
        For r = N To rCount Step N - 1
            .Rows(r).EntireRow.Delete
        Next r
    End With
End Sub


Sub DeleteEveryNthColumn(DeleteRange As Range, N As Integer)
' Deletes every n-th column in DeleteRange
' Example: DeleteEveryNthColumn Selection,2
' Example: DeleteEveryNthColumn Range("A1:D100"),4
Dim cCount As Long, c As Long
    If DeleteRange Is Nothing Then Exit Sub
    If DeleteRange.Areas.Count > 1 Then Exit Sub
    If N < 2 Then Exit Sub
    With DeleteRange
        cCount = .Columns.Count
        For c = N To cCount Step N - 1
            .Columns(c).EntireColumn.Delete
        Next c
    End With
End Sub