Excel – Delete rows and columns using VBA

With the [gs macro]s below you can delete all empty rows or columns within a [gs worksheet] range.
You can also delete every n-th row or column. The [gs macro]s will run faster if you add Application.ScreenUpdating = False to the code.

[tab:Empty Rows]

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

[tab:Empty Columns]

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

[tab:Every N-th Row]

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

[tab:Every N-th Column]

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

[tab:END]