You are reading the article Delete Blank Columns In Excel (3 Easy Ways + Vba) updated in September 2023 on the website Chivangcangda.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Delete Blank Columns In Excel (3 Easy Ways + Vba)
When I was working as a data analyst years ago, part of my job was to download data from financial databases and then clean this data.
And one of the things I had to do while cleaning the data was to delete any blank columns in the data set.
While you can always manually select columns and delete them one by one, doing so in a large data set, where you have tens or hundreds of columns in every data set, would be inefficient and error-prone.
While there is no inbuilt functionality in Excel to date blank columns in one go, this can be achieved by using a combination of different functionalities.
In this tutorial, I will show you how to delete empty columns in Excel using a couple of different methods (including a simple VBA code).
If you have a small data set such as the one shown below, it’s possible to manually select the blank columns, and delete them.
Below are the steps to delete blank columns manually in the above data set:
The above steps would instantly delete the selected blank column, and shift the remaining data set to the left.
The biggest drawback of this method is that it is manual and inefficient (however, it could be a preferred method with small datasets).
In case you have a large data set with lots of blank columns, it’s better to use the methods covered next
Excel has an inbuilt functionality that allows you to quickly select blank cells (using the Go-To special dialog box as we will see later in this tutorial), but there is no way to quickly select only those columns that are empty.
So we will have to use a workaround to first identify those columns that have only blank cells in them and then delete these blank columns.
Below I have a data set where I have the sales figures of different stores for different items. As you can see there are some columns that are completely empty in the below data set.
Now there are a couple of methods you can use to remove blank columns from the dataset. Let’s have a look at each of these methods in detail.
With large datasets, a better way to delete all blank columns is by inserting a helper row at the top and using a COUNTA formula to identify all the columns that are empty.
Once you have done that, you can use this helper row to quickly select all the blank columns and delete them in one go.
Below I have a data set where I have some blank columns that I want to remove.
Here are the steps to do that using the COUNTA function with a helper row:
Enter the below formula in the first cell of the helper row and copy it for all the cells in the helper row
=IF(COUNTA(A2:A1048576)=0,"Blank","Not Blank")The above formula uses the COUNTA function and calculates the total number of cells that are not empty in the specified range.
This formula is going to return a value greater than zero for all the columns that are not completely empty and zero for any column that is completely empty.
And then I used the IF function to return “Blank” in a cell if the entire column below it is empty, and “Not Blank” if it is not empty.
Now that I can identify all the empty columns by looking at the values in the helper row, I’m going to use the Find and Replace dialog box to quickly select all the cells that have the value “Blank”.
Once I have these cells selected, I can delete the entire column in one go.
Below are the steps two now select all the empty columns in one go:
Select all the cells in the helper row (the one where we entered the COUNTA formula)
In the Find and Replace dialog box, enter the text ‘Blank’ in the ‘Find what’ field
In the ‘Look in’ drop-down, select ‘Values’
Check the option, ‘Match entire cell contents’
Hold the Control key and press the A key. This will select all the cells that were given by the Find and Replace option
In the Delete dialog box, select the ‘Entire Column’ option
The above options would instantly delete all the blank columns in your data set.
Important Notes:
COUNTA function would only return 0 if all the cells in the column are blank. in case there is a header in the column that has no values in it and needs to be removed, you need to adjust the formula so that the header row is not used. For example, you can use the formula =IF(COUNTA(A3:A1048576)=0,”Blank”,”Not Blank”) in case even blank columns have headers
For this formula to work, the columns need to actually be blank. For example, if there are space characters in the cells in the blank column, while they might appear to be empty, the COUNTA function would not consider it as blank.
Let me show you another smart way you can use to quickly delete all the empty columns in Excel.
In this method, we will still be using the COUNTA function to get ‘Blank’ or ‘Not Blank’ in the helper row based on whether the column is empty or not.
But instead of using the Find and Replace dialog box, we will use the Sort option
Below I have the same data set and I want to remove the blank columns.
Below are the steps to insert a helper row to identify columns that are empty:
Enter the below formula in the first cell of the helper row and copy it for all the cells in the helper row
=IF(COUNTA(A2:A1048576)=0,"Blank","Not Blank")The above formula would return the text “Blank” in cells where the column below it is empty and “Not Blank” when the column below it is not empty.
Now I can sort the entire data set using the helper row so that I get all the blank columns together and all the non-blank columns together
Below are the steps to do this:
Select the entire data set, including the helper row
Keep the Sort Order A to Z (in case it’s not that already)
The above steps would sort the data based on the helper row and bring all the blank columns together and non-blank columns together (as shown below).
Once you have all the blank columns together, you can select these in one go, and then delete them.
And once you have deleted the blank columns, you can remove the helper row as well.
While the methods covered above work great, they do require a little bit of a setup using a helper row.
In case you are comfortable with VBA, you may find it a little easier to use as compared with the two helper row methods covered above.
Below is the VBA code that will do this for you:
Sub DeleteBlankColumns()
Dim EntireColumn As Range On Error Resume Next
Application.ScreenUpdating = False
For i = Selection.Columns.Count To 1 Step -1 Set EntireColumn = Selection.Cells(1, i).EntireColumn If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then EntireColumn.Delete End If Next
Application.ScreenUpdating = True
End Sub
The above VBA code uses a simple For-Next loop to go through each column in the selection, and check whether the COUNTA value for all the cells in that column is zero or not.
In case the COUNTA function value is 0, it means that the column is empty and the VBA macro code deletes that column.
And in case the value of the COUNTA function is more than 0, it means that the column is not empty and it is not removed.
How to Use the above VBA Macro Code?
Below are the steps to use the above VBA code to delete empty columns in Excel:
Select the data set that has the blank columns that you want to remove
Copy and paste the above VBA macro code into the module code window
The above steps would instantly run the code which would remove all the empty columns from the selected data set.
Caution: The changes done by the VBA macro code in your worksheet cannot be undone. So it’s always a good idea to create a backup copy of your original data before using the VBA code to delete empty columns
One final method that I want to show you to delete empty columns in Excel is by using the Go To Special dialog box.
While this method is the fastest among all the methods covered in this tutorial so far, you need to be extremely cautious when using this method (especially if you’re working with large datasets).
This is because it can be error-prone and can lead to deleting columns that are not completely blank.
So while I’m covering this method in this tutorial, I would not recommend you use this method, instead use the other methods covered in this tutorial.
If you decide to use this method , you should be absolutely sure that your data set has no blank cells in the columns that are not completely blank
Let’s see how this method works.
Below I have a data set where I have some columns that are empty and I want to remove these columns.
Here are the steps to do this using go to special dialog box:
Select the entire data set
The above steps would delete all the columns where there are blank cells
The Drawback of this method?
One big drawback of this method is that in case you have a couple of blank cells in otherwise filled columns, these would also be selected and these non-blank columns would also be deleted.
Below is an example where I have a couple of blank cells in the Printer and Scanner columns, and these were also selected when I use the ‘Go To Special’ dialog box.
Now if I go ahead with the above ‘Go To Special’ method and delete the empty columns, even the Printer and the Scanner columns would be deleted, which is not what I want.
This is why it is best to avoid this method to delete empty columns as it could also end up deleting some columns just because they had a few blank cells in them.
So these are some of the methods you can use to delete blank columns in Excel.
If you have a small data set and you only have a couple of blank columns, it is better to delete them manually.
And in case you have a large data set, you can either use the VBA method (which is fast and efficient), or use the helper row along with the sort feature or the find and replace feature to quickly select blank columns and delete them.
Note: The methods I have covered in this tutorial can also be used to delete blank rows in Excel. You would have to adjust the methods and the VBA code accordingly
Other Excel tutorials you may also like:
You're reading Delete Blank Columns In Excel (3 Easy Ways + Vba)
Update the detailed information about Delete Blank Columns In Excel (3 Easy Ways + Vba) on the Chivangcangda.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!