Easy methods to Delete Total Row in Excel Utilizing VBA (Examples)

Including and deleting rows is a part of on a regular basis frequent duties when working with Excel.

Whereas you are able to do this simply from the worksheet itself, generally chances are you’ll wish to use the VBA path to delete rows in Excel. These may very well be deleting a selected row, a number of rows within the choice, deleting alternate rows or people who have a selected worth in it.

On this tutorial, I’ll present you tips on how to delete rows in Excel utilizing VBA (a number of eventualities).

So let’s get began!

Delete an Total Row utilizing VBA

To delete a whole row in Excel utilizing VBA, it’s worthwhile to use the EntireRow.Delete methodology.

For instance, if you wish to delete your complete first row in a worksheet, you should use the under code:

Sub DeleteEntireRow()
Rows(1).EntireRow.Delete
Finish Sub

The above code first specifies the row that must be deleted (which is completed by specifying the quantity in bracket) after which makes use of the EntireRow.Delete methodology to delete it.

You can too delete a number of rows by specifying these rows within the code.

For instance, the under code will delete row #1, 5 and 9 within the worksheet:

Sub DeleteEntireRow()
Rows(9).EntireRow.Delete
Rows(5).EntireRow.Delete
Rows(1).EntireRow.Delete
Finish Sub

The above code makes use of the identical logic, the place it specifies the row numbers and Excel will delete these rows one after the other.

IMPORTANT: Once you’re deleting rows with one thing just like the above code, bear in mind to start out deleting from the underside after which go up. For instance, in case you begin on the high and delete row 1 first, all of the rows under it will be shifted one row up and the numbering could be off (as row 5 would turn out to be row four and so forth)

Delete All Rows within the Choice

In case you wish to delete all of the rows in a specific vary of cells, you should use the VBA macro code under:

Sub DeleteEntireRow()
Choice.EntireRow.Delete
Finish Sub

The above code applies to the EntireRow.Delete methodology to your complete choice.

Delete Alternate rows (or Delete Each Third/Fourth/Nth Row)

Generally, chances are you’ll get an information dump the place each second row (or third, fourth or Nth rows) is ineffective and must be deleted.

I used to work with monetary knowledge the place each second row was empty and needed to be deleted.

That is the kind of state of affairs the place VBA actually shines.

Beneath is the VBA code that may undergo all of the rows within the choice and delete each second row:

Sub DeleteAlternateRows()
RCount = Choice.Rows.Depend
For i = RCount To 1 Step -2
Choice.Rows(i).EntireRow.Delete
Subsequent i
Finish Sub

Let me clarify how this VBA code works.

First, I’ve used a variable RCount to get the whole variety of rows within the choice.

Then I’ve used a For Subsequent loop to run this as many occasions as many rows are there. For instance, if there are 12 rows, this loop will run from 12 to 1 (i.e., 12 occasions). It’s vital to run this from the final row within the choice to the primary as we don’t need the row numbers to alter when a row is deleted.

Additionally, Step -2 is used since we have to delete each different row (from backside to high). In case you wish to delete each third row, you should use -Three.

Throughout the VBA loop, I’ve used the Choice.Rows(i).EntireRow.Delete methodology to delete each alternate row.

Delete Clean Rows with VBA

You can too use the EntireRow.Delete methodology to delete all clean rows.

Beneath is the VBA code that may choose clean cells within the chosen dataset and delete your complete row.

Sub DeleteBlankRows()
Choice.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Finish Sub

The above code makes use of the SpecialCells property to pick and delete all of the cells which can be clean. This is similar methodology that additionally permits us to make use of ‘Go To Particular’ dialog field to pick all clean cells.

As soon as these clean cells are recognized utilizing SpecialCell, these are then deleted utilizing the EntireRow.Delete methodology.

Word: This methodology selects cells which can be clean and don’t verify whether or not your complete row is clean or not. So if anybody cell is empty in a row, this might nonetheless delete your complete row.

Delete Rows with a Particular Phrase/Worth

You can too use a easy VBA code to undergo every cell within the chosen vary and delete all of the rows the place a cell accommodates a selected textual content or worth.

For instance, suppose you’ve got a dataset and I wish to delete all cells which have the textual content Printer in column 2 of the choice.

https://trumpexcel.com/wp-content/uploads/2020/02/Dataset-from-where-delete-entire-row-with-specific-value.png” />

Beneath is the code that may do that:

Sub DeleteRowswithSpecificValue()
For i = Choice.Rows.Depend To 1 Step -1
If Cells(i, 2).Worth = “Printer” Then
Cells(i, 2).EntireRow.Delete
Finish If
Subsequent i
Finish Sub

The above code first counts the whole variety of rows within the choice. This may be sure that the loop is run solely these many occasions. It then makes use of the ‘For Subsequent loop’ to undergo all of the cells in Column 2.

The IF THEN ELSE assertion is then used to verify the worth in every cell in column 2. And in case the worth/textual content matches the desired textual content (which is ‘Printer’ on this instance).

On this instance, I’ve checked whether or not the textual content matches a selected string or not. You can too do that with values. For instance, you’ll be able to delete all rows the place the sale worth is lower than 1000 or greater than 1000.

Word: An vital factor to notice right here is that the loop runs from Choice.Rows.Depend To 1 to verify when a row is deleted, it doesn’t influence the rows above it.

Easy methods to Use This VBA Code

Now let me present you tips on how to use all of the codes talked about on this tutorial to delete your complete row.

You should copy and paste these codes in a module in Excel VB Editor. After getting these codes copied, you’ll be able to then run the macro codes.

Beneath are the steps to repeat and paste these VBA codes in a module:

Maintain the ALT key and press the F11 key (or Perform + Possibility + F11 in Mac). This may open the VB EditorIn the VB Editor, you should have the mission explorer on the left. If you happen to don’t see it, go to the View possibility after which click on on Challenge Explorer.https://trumpexcel.com/wp-content/uploads/2020/02/Click-on-Project-Explorer.png” />Proper-click on any object within the Challenge Explorer (for the workbook wherein you wish to run the code).https://trumpexcel.com/wp-content/uploads/2020/02/Insert-Module.png” />Go to Insert after which click on on Module. This may insert a brand new Module for the workbookhttps://trumpexcel.com/wp-content/uploads/2020/02/Copy-and-paste-to-delete-entire-row-in-the-module.png” />Copy and Paste the above codes within the module.

And to run these codes, you’ll be able to place the cursor anyplace within the code (that you just wish to run) and hit the F5 key (or click on on the inexperienced triangle within the VBA toolbar).

I’ve additionally written an in depth tutorial on other ways to run VBA macro codes in Excel.

In case it’s worthwhile to use any of those codes very often, you too can think about including these to the Private Macro Workbook after which to the QAT. This fashion, the code will likely be obtainable to you in any of your workbooks with a single click on.

So these have been some VBA codes that you should use to delete whole rows in Excel (in numerous eventualities). The identical logic may also be utilized in case you wish to delete columns as an alternative of rows (with the corresponding adjustment within the code examples).

Hope you discovered this tutorial helpful!

You may additionally like the next Excel tutorials: