How to Delete Rows and Columns in VBA Macro

 

In this blog post, you will learn about how to delete rows and columns in VBA macro.

Sometimes, we face the situation when we need to delete some unwanted rows or columns as per our dataset required.

In Excel, we delete the unnecessary rows and columns by selecting the whole rows or whole columns and right-click from the mouse.

Then choose the delete option from the Options pop-up and delete the unnecessary rows and columns.

Suppose we have a dataset (see below image), there is a blank row and a column in it. 

Now we will delete the unwanted empty rows and columns with the help of the VBA macro.

Image-01

How to Delete Unwanted Rows By VBA Macro

Here in the above image, row (4) is blank and unwanted and need to delete from it.

We will use this row number in the VBA macro code.  

We will use the following VBA macro to remove or delete row (4) from the above dataset.

 

Sub Delete_Rows() Range("4:4").EntireRow.Delete 'this code will delete row (4) End Sub

See the image below:-

Image-02

Now execute the above code by pressing F5 or F8 for step by step execution.

Now suppose that we have to delete multiple rows and columns from the dataset then we will use the following VBA macro code:-

Here rows from 4 to 6 are blank and unwanted, which we need to delete or remove.

Sub Delete_Rows() Range("4:6").EntireRow.Delete 'this code will delete row “4” End Sub

How to Delete Unwanted Columns By VBA Macro

Now the same way when we want to delete the unwanted column from the dataset, then we will use the below given VBA code:-

Sub Delete_Columns() Range("C:C").EntireColumn.Delete 'this code will delete the unwanted column End Sub

See the image below:-

Now run the above VBA macro to delete the unwanted column by pressing F5 or F8 for step by step execution.

Image-03

Once we execute the above VBA code, column (C) will be deleted.

Suppose that we want to delete multiple columns within the dataset, then we need to use the below-given code:-

Sub Delete_Columns() Range("C:E").EntireColumn.Delete 'this code will delete the unwanted columns End Sub

When we execute this code by pressing F5 or F8 (for step by step execution), the mentioned columns will be removed.

So I hope you find this tutorial useful.

Please feel free to put your comments or suggestion in the below-given box.

Thanks

Narendra

Related Post For Beginners

Easiest Way to Copy and Paste Data in Excel VBA

How to use the MsgBox in Excel VBA

How to Change the Background Color in Excel VBA

What is Current Region Property in VBA

How to Change Font Color in Excel VBA

How to Change Sheet Tab Color in Excel VBA