Excel VBA Delete Row

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

This article 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 you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Delete Row (wallstreetmojo.com)

How to Delete Row?

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

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

Now, this will delete the first cell. All the right-side values will shift from 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.

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

For example, we have entered a few characters in an Excel sheet.

If we 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 deleted the row using the CELLS property. Now, we will see how to delete by using the ROWS property.

Now, we need to mention what is the row we need to delete. For example, we need to delete the 5th row.

Now, use the “EntireRow” property.

After selecting the property, what do we need to do? First, we need to delete the row.

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

So, this code will delete the 5th row.

Example #4

Delete Multiple Rows by Using Range Object

How do we delete multiple rows?

We can use the VBA RANGE objectUse The VBA RANGE ObjectRange 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. For example, assume you have some values from A1 to A6 cells.

Now, we want to delete the first five rows, so we can reference these rows by using the Range object as “Range (“A1: A5”).“

Sub DeleteRow_Example4() Range (“A1: A5”) End Sub

Now, we want to use the word “EntireRow” property.

Sub DeleteRow_Example4()

Range(“A1:A5”).EntireRow

End Sub

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

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 the cell value in VBACell Value In VBAIn VBA, there are two ways to interact with or get value from a cell: the range method and the cell method.read more. For example, we have “Yes” and “No” values from cells A1 to A10.

We need to delete the rows with the value “No.” We must use the function “IF” with loops to delete all the rows with the value of “No” to perform this task.

The below code will do the job for us.

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, we have the below set of data.

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

Sub DeleteRow_Example6()

Range(“A1:F10”).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

It will identify the blank cells in the range A1 to F10. If it finds any blank cells, it will delete the entire row.

The problem with this code is it will only delete the blank cell’s row in the range A1 to F10. But if any cells are blank in any other cells, it will not delete them. So, keeping this in mind, we have written one more 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 rowsDelete All The Blank Cells RowsThere 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.

This article has been a guide to VBA Delete Row. Here, we discussed how to delete rows using VBA codes and practical examples. Below are some useful Excel articles related to VBA: –

  • Excel VBA Delete ColumnVBA Last RowHow to Code in VBA?Format Number in VBAVBA INT