Hello Excellers, time for some Excel Macro fun again. Today’s Excel Macro tip is one I have been looking forward to sharing with you for a while. Are you looking for a quick way to delete all the rows with specific text in your Excel spreadsheet?. If so, you’ll be glad to know that there is an easy way to do this. In this blog post, we will show you how to delete all rows that contain a specific text string. We will also provide some tips on how to use this technique. So if you’re ready, let’s get started!
This is a question I get asked quite regularly, and it is a really useful piece of code to know. The most common way to tackle this type of problem is is to loop through the entire column in your Excel worksheet. If a cell in the column contains the specific text then instruct Excel to delete the whole row. Seeing as Excel will shift rows upward by default on deletion, then the most logical way to do this is start at the bottom and work upwards.
This looping is quite a slow process which works great for smaller data sets as it is time consuming and slow to execute. It fails somewhat with larger data sets. Even optimising the code by turning off screen updates with the (Application Objects ScreenUpdating set to false) and Calculation properties set to Manual for the following reasons it is still slow. The mains reasons for this speed issue are below.
- The Delete Row command triggers Excel to recalculate, which if there are a lot of formulas in your worksheet is time consuming.
- Looping through all of the cells in a column or even the used cells in a column is also time consuming.
Use Auto Filter
So let’s assume we have the larger data set, and want to carry out this procedure in a more efficient way. Well, I choose to use the Autofilter Method. This method is faster, but the data is required to be set up in a specific way. The data must be set up in a table format, with the first row containing field headers or column names. (Which mine is!).
What Does The Macro Do?
This Macro will identify and filter all of the rows that contain the text string your specify, then delete them.
Step 1. Open Visual Basic Editor Insert A New Module.
So, the first step in this macro is to open Visual Basic. There are two ways to do this. Hit ALT +F11 or Developer Tab | Visual Basic | Insert Menu | Module. The module can be inserted into the current Excel workbook (this is typically used when the macro is specific to that workbook), or in your Personal Macro Workbook (PMW). The PMW is useful if you want to be able to run the macro when any Excel workbook is open. So, in this case, the macro COULD be used on any Excel workbook, so makes sense to save it in my PMW.
So, as soon, as Excel starts any macro saved in this location become available to use. The final part of the step is to the Excel macro, this this example the macro has the name DeleteRowsMeetingCriteria. Notice Excel inserts the End Sub line of code automatically. Any other lines of code are written between these two.
Step 2. Turn Off Filters In Active Worksheet.
We specify we are using the Active Workskeet, to ensure no other worksheet is affected by the macro actions..The Auto Filer is turned off (in case it is on) by Excel.
Step 3. Apply The Filter.
The Filter is applied to our data range which is set to Column A, down to the last row with data. The word to filter is Total. the data is now are filtered on any rows that contain “Total”, in Column A, (or Column 1).
Step 4 . Delete Rows!
Any rows now that are visible in the filtered data are deleted, using the SpecialCells(12).EntireRow.Delete but we have specified that we are offsetting the deletion by 1 cell. This is to accommodate the field or column headers which we do not want to delete.
Step 5. Remove AutoFilter.
In this last step the autofiter is removed once again to return the data set back. The macro then ends with the End Sub line of code.
Finally, it is time to test the Excel macro to delete rows with specific text.
Copy The Code.
Feel free to copy the code below to use in your own Excel workbooks. Please backup any Excel workbooks before you run any code.
Sub DeleteRowsMeetingCriteria()
With ActiveSheet
.AutoFilterMode = False
With Range(“A1”, Range(“A” & Rows.Count).End(xlUp))
.AutoFilter 1, “Total”
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
More Excel Tips?
Sign up for my Excel Newsletter for three free Excel tips every month. Join thousands of other Excel users in the community. Come Say hello!.
