how to delete blank columns in excel spreadsheet

In Step 4, the macro uses the iCounter variable as an index number for the Columns collection of MyRange. For instance, Range('A1:E11').Columns(2) points to the second column in the range ( column B).

When working with a range, you can explicitly call out a specific column in the range by passing a column index number to the Columns collection of the range.

In all, Step 3 tells Excel that we want to start at the last column of the chosen range, moving backward until we get to the first column of the range. Because we specify Step -1, Excel knows we are going to increment the counter backwards moving back one increment on each iteration. Note that we are using the Step-1 qualifier.

In this step, the macro sets the parameters for our incremental counter to start at the max count for the range ( ) and end at 1 (the first row of the chosen range).

Note that if we wanted to specify an actual range or a named range, we could simply enter its name: Range('MyNamedRange'). The UsedRange property gives us a range that encompasses the cells that have been used to enter data. Step 2 fills the MyRange variable with the UsedRange property of the ActiveSheet object.This variable serves as an incremental counter. The other variable is a Long Integer variable called iCounter. This is an Object variable that defines the target range. The first variable is an object variable called MyRange. We keep doing that same delete for every loop, each time incrementing the counter to the previous column. If the entire column is indeed empty, we remove the column. We then establish a counter that starts at the last column of the used range, checking if the entire column is empty. In this macro, we are using the UsedRange property of the ActiveSheet object to define the range we are working with. If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then

'Step 4: If entire column is empty then delete it. 'Step 3: Start reverse looping through the range.įor iCounter = To 1 Step -1 3.1 Don't see personal.xlsb in your project window?.

How to delete blank columns in excel spreadsheet