how to delete multiple rows in excel

Excel VBA Delete Row

Normally in excel worksheet, we have two different methods to delete rows one being the keyboard shortcut and another by using the right-click and insert method but in VBA we have to use delete command and worksheet statement to delete any rows together, the trick to this is that if we need to delete a single row we give a single row reference but for the multiple columns we give multiple row references.

Using VBA Delete Row Method, we can delete all the blank rows, and we can delete the row based on cell value. We can also delete the entire row if any of the cells are blank.

In this article, we will discuss the method "VBA Delete Row." Keep yourself occupied for the next 15 to 20 minutes to learn about the concept.

You are free to use this image on your website, templates etc, Please provide us with an attribution link Article Link to be Hyperlinked
For eg:
Source: VBA Delete Row (wallstreetmojo.com)

How to Delete Row?

You can download this VBA Delete Row Excel Template here – VBA Delete Row Excel Template

Example #1

In VBA, we need to mention the row we are deleting.

Code:

            Sub            DeleteRow_Example1()            Cells(1, 1)            End Sub

Cells (1, 1) means first-row first column i.e., A1 cell. Then we use the method "delete."

Code:

            Sub            DeleteRow_Example1() Cells(1, 1).Delete            End Sub

Now this will delete the first cell. All the right side values will shift one cell to the left.

Example #2

If you want to delete the entire row, we need to use the property "EntireRow," then we need to use the method "delete" to delete the entire row of the cell we have selected.

Code:

            Sub            DeleteRow_Example2() Cells(1, 1).EntireRow.Delete            End Sub

For example, I have entered a few characters in an excel sheet as follows.

Now, if I run this code, it will delete the entire row, not a single cell.

Example #3

We can delete the row by using several ways. In the above example, we have deleted the row by using CELLS property. Now we will see how to delete by using ROWS property.

Now we need to mention what is the row we need to delete. Let's say we need to delete 5^th row.

Now use the property "EntireRow" property.

After selecting the property, what we need to do i.e., method. We need to delete the row.

Code:

            Sub            DeleteRow_Example3() Rows(5).EntireRow.Delete            End Sub

So, this code will delete the 5^th row.

Example #4

Delete Multiple Rows by Using Range Object

How do we delete multiple rows?

We can use the VBA RANGE object The range is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns. read more to delete more than one row. Assume you have some values from A1 to A6 cells.

Now I want to delete the first 5 rows, so I can reference these rows by using the Range object as "Range ("A1: A5")"

Code:

            Sub            DeleteRow_Example4()            Range ("A1: A5")            End Sub

Now I want to use the word "EntireRow" property.

Code:

            Sub            DeleteRow_Example4()  Range("A1:A5").EntireRow            End Sub

In this entire row, we need to perform the method of deleting, so use the Delete method.

Code:

            Sub            DeleteRow_Example4()  Range("A1:A5").EntireRow.Delete            End Sub

Now this will delete the selected rows.

Example #5

Delete Rows Based On Cell Value

We can also use this "EntireRow.Delete" method to delete the row based on cell value in VBA. For example, I have Yes & No values from cell A1 to A10.

Now we need to delete the rows which have the value "No." To perform this task, we need to use the function "IF" with loops to delete all the rows which have the value of "No."

The below code will do the job for us.

Code:

            Sub            DeleteRow_Example5()            Dim            k            As Integer            For            k = 10            To            1            Step            -1            If            Cells(k, 1).Value = "No"            Then            Cells(k, 1).EntireRow.Delete            End If            Next            k            End Sub

Example #6

Delete All the Blank Cells Rows

There are situations where we need to delete the entire row if any of the cells in the range are blank. For example, I have below set of data.

All the colored cells are blank, so I need to delete the entire row. We can perform this task with two sets of code. Below is the code.

Code:

            Sub            DeleteRow_Example6()  Range("A1:F10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete            End Sub

This will identify the blank cells in the range A1 to F10.IF any blank cells are found, they will delete the entire row.

The problem with this code is it will only delete the blank cell's row only in the range A1 to F10, but if any cells are blank in any other cells, it will not delete. So keeping this in mind, I have written one more code.

Code:

            Sub            DeleteRow_Example7()            Dim            RangeToDelete            As            Range            Dim            DeletionRange            As            Range            Set            RangeToDelete = Application.InputBox("Please select the range", "Blank Cells Rows Deletion", Type:=8)            Set            DeletionRange = RangeToDelete  RangeToDelete.SpecialCells(xlCellTypeBlanks).EntireRow.Delete            End Sub

When you run this code, firstly, it will ask you to select the range with an input box appearing in front of you.

After selecting the range, you need to click on OK. It will delete all the blank cells rows There are several methods for deleting blank rows from Excel: 1) Manually deleting blank rows if there are few blank rows 2) Use the formula delete 3) Use the filter to find and delete blank rows. read more in the selected range.