ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

VBA: Loop to Delete All Blank Columns in Excel Range

Updated on July 10, 2013

From time to time when working in Excel you may find the need to quickly remove blank columns from a block of data. While removing columns one by one is feasible in many cases, if there are tens or hundreds of columns that need deleted a Visual Basic Macro make quick work of this seemingly time consuming task.


Working in the correct direction

If may seem logical to work from left to right when deleting columns on your spreadsheet, after all this is the direction most of us read. However, if you were to try this you would likely find it very tricky. As each column is deleted the remaining unevaluated column numbers will change. For this reason, it is much simpler to work from right to left, it is best to always use this method.

Two Primary Macro Components


Save last column number in used range

c=ActiveSheet.Cells.SpecialCells(xlLastCell).column


Use Counta Function to determine if entire column is blank

If WorksheetFunction.CountA(Columns(c)) = 0

The Finalized Macro

Sub DeleteBlankColumns()

Dim c As Integer
'declare c as variable for column number

c = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
'save last column number in used range

Do Until c = 0
'Loop for each column number until it equals 0
    
    If WorksheetFunction.CountA(Columns(c)) = 0 Then
    'Check if entire column is blank
        Columns(c).Delete
        'if column is blank delete column
    End If
    'closes if statement
    c = c - 1
    'proceeds to the next lowest column number
Loop

End Sub

Comments

    0 of 8192 characters used
    Post Comment

    Click to Rate This Article