Hello Excellers, time for some Excel Macro fun again. Todays Excel Macro tip is one I have been looking forward to sharing with you for a while – how to delete all row in your Excel sheet that contain a specific text string.
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 is to loop through the entire column, and if a cell in the column contains the specified text string then 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 and works for smaller data sets as it is time consuming and slow to execute, but not so good on larger data sets, even with the Application Objects ScreenUpdating and Calculation properties set to Manual for the following reasons:-
- 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.
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, which asssumes our data set is set up in a certain way, with the first row containing field headers or column names. (Which mine is!).
Download Macro Mondays Example Files.
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.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Menu – Module
Step 1. We specify we are using the Active Workskeet, to ensure no other worksheet is affected by our actions, and at this stage the Auto Filter is first turned off (in case it is on) by Excel.
Step 2. The Filter is applied to our data range which is set to Column A, down to the last row with data.
Step 3. We now are filtered on any rows that contain “Total”, in Column A, (or Column 1)
Step 4. 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. Finally the autofiter is removed once again.
Step 6. Test Your Macro!!
.AutoFilterMode = False
With Range(“A1”, Range(“A” & Rows.Count).End(xlUp))
.AutoFilter 1, “Total”
On Error Resume Next
.AutoFilterMode = False
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.