ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address