How to Remove Blank Columns in Excel? (Formula + VBA)

by

Sometimes you may get an Excel dataset that has blank columns.

In most cases, this is undesirable and you would want to delete them. While there is no dedicated feature in Excel to delete blank columns in Excel, this is quite easy to do.

In this tutorial, I will show you four techniques for removing blank columns in Excel.

Note: We strongly recommend that you make a backup of your workbook before using any of the techniques so that in case anything goes wrong you have a backup of your data to revert to. 

Method #1: Remove the Blank Columns Manually

In this method, we select each blank column and delete it manually. 

We use the following dataset which has two blank columns, columns C and G, to show how this technique works.

Dataset with blank columns

Below are the steps to delete blank columns manually:

  1. Select column C by clicking its column header, press and hold down the Ctrl key, select column G by clicking its column header, and then release the Ctrl key. 
Select the blank columns
  1. Right-click inside any of the selected columns and choose Delete on the shortcut menu that appears.
right click and delete

The empty columns are removed from the dataset.

Blank columns removed

This technique is the best for small datasets but inefficient for large datasets that may have tens or even hundreds of blank columns. 

Note: You can also select multiple columns in one go and then delete them together. To select multiple columns, hold the Control key and then select the columns you want to select by clicking the column headers

The following two methods come in handy when dealing with huge datasets.

Also read: Remove Blank Rows in Excel (5 Ways + VBA)

Method #2: Remove Blank Columns By Using a Formula With Find and Replace 

In this technique, we first identify the empty columns using a formula and then delete them.

We will use the following example dataset that has columns C and G that appear blank to show how this technique can be used.

Dataset with blank columns

We say that columns C and G appear blank because we may not be too sure that the columns do not contain data in some cells that are way down the columns or even contain data in form of empty strings.

That is why we want to use a formula to first identify the empty columns before deleting them. 

Below are the steps to identify all the columns that are empty:

  1. Insert a helper row on top of the dataset by right-clicking the header of row 1 and selecting Insert on the shortcut menu that appears.
right-click and insert
  1. Enter the following formula in cell A1 of the helper row:
=IF(COUNTA(A2:A1048576)=0,"Blank","Not Blank")
Enter formula in first cell of helper row
  1. Press the Enter button on the Formula bar to enter the formula.
  2. Drag the fill handle across to column H to copy the formula to the columns.
Helper row formula result

The formula has returned the word Blank on top of all the empty columns. We can now remove the empty columns at once using the steps below:

  1. Select all the cells in the helper row.
  2. Press Ctrl + F to open the Find and Replace dialog box.
  3. In the Find and Replace dialog box that opens, do the following:
  • Type the word Blank in the Find what drop-down.
  • Open the Within drop-down and select Sheet.
  • Open the Search drop-down and select By Rows.
  • Select the Match entire cell contents option.
  • Click the Find All button.
Find and replace settings

This returns the cell references of all the cells that contain the word Blank.

  1. Press Ctrl + A to select all the cells that contain the word Blank.
Select all cells that have the word blank
  1. Right-click any of the selected cells and select Delete on the shortcut menu that appears.
right-click and delete
  1. In the Delete dialog box, select the Entire column option and then click OK.
Delete entire column

All the blank columns are removed and data is shifted to the left. 

Blank columns removed

You can now remove the helper row. We now have our dataset where the empty columns have been deleted.

Remove the helper column

Explanation of the formula

=IF(COUNTA(A2:A1048576)=0,"Blank","Not Blank")

This formula uses the COUNTA and IF functions.

The COUNTA function tallies the number of non-empty cells in a range.

The IF function checks whether a condition is met and returns TRUE if it is met and FALSE if it is not met. 

  • COUNTA(A2:A1048576)=0 In this part of the formula the COUNTA function returns TRUE if all the cells in the range A2: A1048576 are empty and returns FALSE if all the cells in the range are not empty. Note that the last row in all the new versions of Excel is row number 1,048,576.
  • IF(COUNTA(A2:A1048576)=0, “Blank”, “Not Blank”) In this part of the formula the IF function returns Blank if the COUNTA function returns TRUE and returns Not Blank if the COUNTA function returns FALSE.

Note: If the columns only have column headers as in the example below, such columns are not considered blank. In such case, you need to modify the formula so it doesn’t include the headers

blank columns with headers

Method #3: Remove Blank Columns By Bringing Them Together Using the Sort Option 

If our dataset has tens or even hundreds of non-contiguous blank columns, it is efficient to first bring them close to each other before deleting them.

Below I have a dataset where I want to remove all the empty columns.

Dataset with blank columns

First, I would use a formula to identify all the columns that are blank, and then I will use the Sort option to bring all the blank columns together so these can be removed in one go.

Below are the steps to identify all the columns that are empty:

  1. Insert a helper row on top of the dataset by right-clicking the header of row 1 and selecting Insert on the shortcut menu that appears.
right-click and insert
  1. Enter the following formula in cell A1 of the helper row:
=IF(COUNTA(A2:A1048576)=0,"Blank","Not Blank")
Enter formula in helper row
  1. Copy the formula across column H to get the formula result on all the cells in the helper row.
Apply formula to the entire helper row

The formula returns the word ‘Blank’ on top of all the empty columns, and the word ‘Not Blank’ in all the columns that are not blank.

Now that we know all the columns are empty, below are the steps to use the Sort option to bring all the empty columns together.

  1. Select the helper row which contains formulas by clicking its header.
Select the helper row
  1. Click the Data tab, and select Sort in the Sort & Filter group.
Click the Sort option
  1. Select Expand the selection in the Sort Warning dialog box that opens up and click the Sort button.
Select the expand the selection option
  1. In the Sort dialog box, click the Options button.
click on the options button
  1. Select Sort left to right in the Sort Options dialog box that appears and then click on the OK button.
select sort left to right
  1. In the Sort dialog box, open the Row Sort by drop-down and select Row 1, open the Sort On drop-down and select Cell Values, open the Order drop-down and select A to Z, and click OK:
sort by row 1

All the blank columns are brought close to each other on the left of the worksheet as shown below:

all blank columns are stacked together
  1. Select the first blank column by clicking its header, press and hold down the Shift key, select the last blank column by clicking its header, and then release the Shift key.
select all the blank columns
  1. Right-click the selected blank columns and select Delete on the shortcut menu that appears.
right click and delete

The blank columns are removed from the dataset:

Blank columns removed

We can now go ahead and remove the row we inserted on top of the dataset.

Your dataset is now free of blank columns.

Blank columns removed

Method #4: Remove Empty Columns Using VBA Macro Script

In this technique, we first create a subroutine in Excel VBA and then run the subroutine to remove the empty columns from the dataset.

We will use the below example dataset where columns C and G are blank and we need to remove these blank columns.

Dataset with blank columns

Follow the below steps to do this:

  1. Click the Developer tab and then click the Visual Basic icon in the Code group.  This opens the Visual Basic Editor.
click the visual basic icon
  1. Open the Insert menu and click the Module item. This inserts a new module in the Visual Basic Editor.
insert a module
  1. Copy the following subroutine and paste it into the module.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub RemoveBlankColumns()
Dim TargetRange As Range
Dim WholeColumn As Range
    Dim i As Long
    On Error Resume Next
    Set TargetRange = Application.InputBox( _
        "Select a range:", "Remove Blank Columns", _
        Application.Selection.Address, Type:=8)
If Not (TargetRange Is Nothing) Then
    Application.ScreenUpdating = False
    For i = TargetRange.Columns.Count To 1 Step -1
        Set WholeColumn = TargetRange.Cells(1, i).EntireColumn
            If Application.WorksheetFunction.CountA(WholeColumn) = 0 Then
                WholeColumn.Delete
            End If
    Next
Application.ScreenUpdating = True
End If
End Sub
  1. Save the subprocedure and save the workbook as a Macro-Enabled workbook.
  2. Click inside the procedure and press F5 to run the code.
  3. When the InputBox pops up, switch to the worksheet containing the dataset you want to work with, select the dataset and click OK.
select the range of cells

You will be taken back to the Visual Basic Editor.

  1. Click the View Microsoft Excel button on the toolbar to switch back to the active worksheet.
Click the View Microsoft Excel icon

You will see that the empty columns have been removed from the dataset:

Blank columns removed

In this tutorial, we have looked at four techniques for removing blank columns in Excel. We can remove the blank columns manually, use a formula to identify the blank columns then delete them, and use Excel VBA. We hope you found this information beneficial.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment