How to delete entire columns based on header value in Excel?
In many cases, you may need to delete the entire columns based on header value in Excel. For example, you need to delete all columns which contain value “old” in the header. In this tutorial, we will show you methods of deleting entire columns based on header value in details.
Delete entire columns based on header value with VBA code
You can delete entire columns based on header value with VBA code. Please do as follows.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic Application window.
2. In the Microsoft Visual Basic Application window, click Insert > Module. And then copy and paste below code into the Module window.
VBA code: delete entire columns based on header value
Sub DeleteSpecifcColumn() Set MR = Range("A1:D1") For Each cell In MR If cell.Value = "old" Then cell.EntireColumn.Delete Next End Sub
3. Press the F5 key to run the code, the entire column with a header equals to the specified text will be deleted immediately.
Delete entire columns based on header value with Kutools for Excel
With Kutools for Excel’s Select Specific Cells utility, you can easily select all columns which contains the certain header value first, and then delete these selected columns at once. Please do as follows.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the range which contains the columns you need to delete, and then click Kutools > Select > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog box, select the Entire column option in the Selection type section, and in the Specific type drop-down list, select Equals option, then type the header value in the following box. And finally click the OK button. See screenshot:
3. Click the OK button in another popping up dialog box, then the columns which contain specified header value are selected immediately.
4. Right click the column header of selected column, and then click Delete from the right-clicking menu. Then all selected columns are deleted at once.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!