Delete rows and columns

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

Document last updated 1999-12-20 12:51:27 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-11-15 23:43:21 CET):

Re: could not start the macro?
You will have to use the example macros above from another macro that doesn't require any input, e.g. like this:
Sub TestDelete()
DeleteEmptyRows Range("A1:D100")
End Sub

leo from Utrecht, Netherlands wrote (2006-11-15 21:08:39 CET):

pasted the routine in a module, but could not start the macro?
It must be me, but I pasted in the code to delete empty columns in a separate module and expected to be able to hit the start button to run it. But that didn't work. How can I tell excel what the DeleteRange is? I probably don't understand the (DeleteRange as Range) Can you please explain?

Leomoey@hotmail.com