The article will provide you with some quick tricks to delete empty columns with a header in Excel. While working on an Excel worksheet, you may need to deal with many things. One of them may be deleting empty columns with headers. I have added four suitable methods here. You will find the necessary images attached with every step. Hopefully, you will find the processes interesting.
How to Delete Empty Columns with Header in Excel: 4 Ways
Here is the dataset I am going to use. The dataset has three filled columns and one empty column. The columns are B, C, D, and E called ID, Products, Price, and Delivery Date. Here, the Price column is empty. I need to remove this column from the data table. I will show four methods here to Excel delete columns with a header. Follow the procedures step by step.
1. Deleting Empty Columns With Header by Excel Editing Tools
In the first method, I will show you the process of deleting empty columns with a header. Here, in the dataset, you will find the Price column is empty. Follow the procedure step by step.
Steps:
- Select the D column first by clicking where the arrow sign is placed.
- Right-click on column D and a pop-up window will be seen.
- Next, select the Delete option.
- This command will delete the entire D column.
This is how you can delete empty columns with a header in Excel. This method is the most common and easiest way of deleting columns.
Read More: How to Delete Columns Based on Header Using VBA in Excel
2. Implementing Find & Search Option
Here, I will use the search and find option to delete empty columns with headers in Excel. This is also a short process. You can see the Price column is empty here. I will delete this column.
Steps:
- First, go to your Home tab in your Toolbar.
- Then, select the Editing option.
- After that, select the Find & Select.
- A window will pop up. Click on the Go to Special option there.
- In the Go to Special option, select the Blanks option. The blank columns of the data table will be selected.
- Right-click on the selected cell.
- Then, select the Delete option.
- Another window will pop up. Select the Entire column option from there.
- As a result, Excel deletes the entire D column.
This is how you can delete empty columns in Excel from the data table manually.
Similar Readings
- How to Delete Unused Columns in Excel
- Delete Infinite Columns in Excel
- How to Delete Multiple Columns in Excel
- Delete Multiple Columns in Excel with Condition
- VBA to Delete Column in Excel
- Delete Multiple Columns by Number Using VBA in Excel
3. Using Keyboard Shortcut
In this method, I will use a keyboard shortcut to delete the empty cell with a header in Excel. Follow the following steps.
Steps:
- Select the whole D column as it has the empty cells.
- Then, press the Ctrl and – buttons one after another.
- You will see the whole D column is deleted and the column E replaces the position.
This is how you can delete empty columns with a header by using keyboard shortcut.
Read More: How to Delete Columns with Specific Text in Excel
4. Applying Excel VBA to Delete Empty Columns with Header
You can delete an empty column by inserting a VBA code. However, this is a smart process. This method can increase your Excel skills hopefully. Follow the following steps to become familiar with the procedures. You can also see the images attached with the steps for better understanding.
Steps:
- First, press Alt+F11 to open the Microsoft Visual Basic for Applications.
- Open a new module and write down the following code.
Sub MacroToDeleteEmptyColumns()
Dim sS_xEnd As Long
Dim sS_K As Long
Dim sS_xD As Boolean
On Error Resume Next
sS_xEnd = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If sS_xEnd = 0 Then
MsgBox "No data is found on """ & ActiveSheet.Name & """ .", vbExclamation, "Deleting columns with headers"
Exit Sub
End If
Application.ScreenUpdating = False
For sS_K = sS_xEnd To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(sS_K)) <= 1 Then
Columns(sS_K).Delete
sS_xD = True
End If
Next
If sS_xD Then
MsgBox "Blank and columns with headers are deleted.", vbInformation, _
"Deleting columns with headers"
Else
MsgBox "There is no Columns to delete.", _
vbExclamation, ""
End If
Application.ScreenUpdating = True
End Sub
Here, we declared some variables in the VBA code to identify the columns with empty cells and used the If Statement to apply some conditions to detect those empty columns.
- After running the code, you will find the following result.
You can use a VBA code to delete empty columns with header.
Read More: VBA Macro to Delete Columns Based on Criteria in Excel
What to Do If Deleting Empty Columns Does Not Function in Excel
Sometimes deleting empty columns does not work as the column is not empty actually. Maybe the column looks like empty. Because sometimes, some characters cannot be visible in Excel cells. To detect the culprit cell, you need to follow the procedures given below.
Steps:
- Select the first cell of the column first.
- Press Ctrl and the Down arrow button simultaneously.
- You can see this command is unable to select all the empty cells below the first one. Because there is one cell that has invisible data and it’s blocking the command to select other empty cells. Here, cell D6 is not empty as the cells below it cannot be selected.
This is how you will detect the empty-looking cell but not empty.
Read More: How to Delete Hidden Columns in Excel
Things to Remember
- You should bear in mind that every empty-looking column must be empty for deleting the cells.
Download Practice Workbook
Please download the workbook to practice yourself.
Conclusion
In this article, I have tried to explain the procedures to delete empty columns with a header in Excel. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. Additionally, you will find such interesting blogs on our website. Moreover, I hope you have enjoyed the whole tutorial. However, if you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.