- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: Loop to Delete All Blank Columns in Excel Range
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