How to Delete Multiple Columns by Number Using VBA in Excel (4 Ways)

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 with the column letters. Let’s dive into the examples to get a clear understanding of the VBA codes to use.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Examples to Delete Multiple Columns by Number Using VBA in Excel

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 name of some products with their category and available stocks.

Delete Multiple Columns by Numbers using VBA in Excel

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-

Range(Columns(start_column_num), Columns(end_column_num)). Delete

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 New 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.

Delete Multiple Columns by Numbers using VBA in Excel

To delete these adjacent columns, let’s copy and paste the following code in the visual code editor.

Sub DeleteAjacentColumns()
Range(Columns(6), Columns(8)).Delete
End Sub

Delete Multiple Columns by Numbers using VBA in Excel

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 Multiple Columns in Excel


2. Use VBA to Delete Multiple Non-Adjacent Columns by Number in Excel

Let’s try to delete some nonadjacent 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.

Delete Multiple Columns by Numbers using VBA in Excel

To delete these nonadjacent columns (3-5 and 9-10), let’s put the following code in the visual code editor.

Sub DeleteNonAjacentColumns()
Union(Range(Columns(3), Columns(5)), Range(Columns(9), Columns(10))).Delete
End Sub

Now press F5 to run and delete the nonadjacent 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 (9 Criteria)


Similar Readings


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.

Delete Multiple Columns by Numbers using VBA in Excel

This is the code to run in the visual basic code editor.

Sub SelectDeleteAjacentColumns()
Worksheets("products").Select
Range(Columns(6), Columns(8)).Delete
End Sub

Delete Multiple Columns by Numbers using VBA in Excel

We finally got columns 6-8 deleted in the “productsworksheet.

Delete Multiple Columns by Numbers using VBA in Excel

Related Content: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)


4. Run a VBA Code to Delete Multiple Columns by Number Based on User Input in Excel

We can use Excel’s 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. So we need to take the start_column_num and the end_column_num of the following code as user input.

Range(Columns(start_column_num), Columns(end_column_num)). Delete

Let’s follow the simple steps to accomplish this.

  • Put the following code in the visual code editor 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

Delete Multiple Columns by Numbers using VBA in Excel

  • Put 6 in the “Starting Column no of the DELETE Range” named input box and hit OK.

  • And put 8 in the “Ending Column no of the DELETE Range” named input box and hit OK.

  • This is the final output.

Delete Multiple Columns by Numbers using VBA in Excel

Related Content: How to Delete Infinite Columns in Excel (4 Methods)


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 vegetable category. But to define as column numbers we need to use the following code structure

Range(Columns(start_column_num), Columns(end_column_num))


Conclusion

Now, we know how to delete multiple columns in Excel by numbers with 4 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo