Delete rows and columns

 1999-12-20    Worksheets    0    65

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


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.