Al Arafat Siddique
In this article, we’ll illustrate how to delete multiple columns by number using VBA code in Excel with 4 different examples. As deleting columns is a common task in the Excel worksheet, we should know how to delete columns by defining column numbers just like we can delete using the column letters. Let’s dive into the examples to get a clear understanding of the VBA codes to use.
How to Delete Multiple Columns by Number Using VBA in Excel: 4 Ways
To illustrate how to delete multiple columns by number in Excel using VBA code, we’re going to use a sample dataset. The dataset contains the names of some products with their category and available stocks.
To delete multiple columns, we need to give multiple column references as a Range object to the delete command. We can make the column reference using the column number (1, 2, 3…) just like the column letter (A, B, C…), shown in the above screenshot. The generic code for deleting columns in Excel using VBA code is like-
The Range() function wraps an entire region to operate a command onto it. In the following examples, we’ll use column number as the Column Reference to delete multiple columns.
Write Code in Visual Basic Editor
Follow the steps to open the Visual Basic Editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the Module.
Now that a new module is opened, write some code there and press F5 to run.
1. Delete Multiple Adjacent Columns by Number Using VBA in Excel
In this example, we want to delete the products that belong to the Vegetable category. That means we need to delete columns 6-8 that contain vegetable products.
To delete these adjacent columns, let’s copy and paste the following code into the module.
Sub DeleteAjacentColumns()
Range(Columns(6), Columns(8)).Delete
End Sub
After running the code by pressing F5, the output gives us the dataset with deleted columns 6,7, and 8.
Read More: How to Delete Columns Based on Header Using VBA in Excel
2. Use VBA to Delete Multiple Non-Adjacent Columns by Number in Excel
Let’s try to delete some non-adjacent columns in this example using VBA code. Here we want to delete products of both Fruit and Meat categories i.e., columns 3-5 and columns 9-10.
To delete these non-adjacent columns (3-5 and 9-10), let’s put the following code in the module.
Sub DeleteNonAjacentColumns()
Union(Range(Columns(3), Columns(5)), Range(Columns(9), Columns(10))).Delete
End SubNow press F5 to run and delete the non-adjacent columns.
Code Explanation:
In this code, we used the Union method so that we can use multiple Range objects. We set two sets of columns to delete using the Range object and then used them inside the Union method of VBA Excel separated by a comma. We can similarly define as many sets of columns we want to delete from our dataset.
Read More: VBA to Delete Column in Excel
Similar Readings
- How to Delete Column and Shift Left Using VBA in Excel
- How to Delete Column in Excel Without Affecting Formula
- How to Delete Multiple Columns in Excel with Condition
- How to Delete Columns with Specific Text in Excel
3. Define Worksheet Name and Delete Multiple Columns by Number in Excel VBA
It is always a good practice to first select the worksheet that contains our valuable data and then apply VBA to code to make some operations on it. In this example, we’ll first select the active worksheet named products and delete products with the Vegetable category.
This is the code to run in the module.
Sub SelectDeleteAjacentColumns()
Worksheets("products").Select
Range(Columns(6), Columns(8)).Delete
End Sub
We finally got columns 6-8 deleted in the products worksheet.
Related Content: VBA Macro to Delete Columns Based on Criteria in Excel
4. Run a VBA Code to Delete Multiple Columns by Number Based on User Input in Excel
We can use the VBA InputBox function to take the deleting column range as user input. Let’s say we want to delete columns 6-8 i.e., the products with the Vegetable category. We need to take the start_column_num and the end_column_num of the following code as user input.
Let’s follow the simple steps to accomplish this.
- Put the following code in the module and press F5 to run.
Sub DeleteColumnsByUserInput()
Dim SCol, ECol As Integer
SCol = Application.InputBox( _
Prompt:="Starting Column no of DELETE Range", _
Type:=1)
ECol = Application.InputBox( _
Prompt:="Ending Column no of DELETE Range", _
Type:=1)
Range(Columns(SCol), Columns(ECol)).Delete
End Sub
- Put 6 in the Starting Column no of the DELETE Range named input box and hit OK.
- Put 8 in the Ending Column no of the DELETE Range named input box and hit OK.
- This is the final output.
Related Content: How to Delete Infinite Columns in Excel
Things to Remember
To define the column numbers in the VBA code, we just cannot put them like the column letters. We can define Range(“F: H”) easily to delete the products with the Vegetable category. But to define them as column numbers we need to use the following code structure-
Range(Columns(start_column_num), Columns(end_column_num))
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to delete multiple columns in Excel by number with 4 different examples. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
