Delete rows and columns using VBA in Microsoft Excel





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


One thought on “Delete rows and columns using VBA in Microsoft Excel

  1. “Hi,

    Thanks for your tips.
    However, do you know how we can batch-delete the unused (or mis-referring) names in a workbook?

    Thanks again.

    Hubert”

Leave a Reply

Your email address will not be published. Required fields are marked *


2 + = four

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>