Time for some more Macro Fun on #macromondays. Today I want to share with you how to quickly and easily delete any rows of data that do NOT contain a string that you specify. I find this a quick and easy way to search through a lot of Excel rows and get rid of any rows of data that I know I do not need.
Let’s use an example. Every week you receive a data extract which contains hundreds of thousands of data rows, but we need to delete all of the rows in the data that do not contain the string “Invoice”. We can write a Macro to do this with a couple of clicks rather than using the filtering then deleting options in Excel, and as an added bonus we can save this Macro in our Personal Macro Workbook to use over and over again in any workbook.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or DOWNLOAD example worksheets you can do so by clicking on the links below.
What Does The Macro Do?
This Macro will identify and filter all of the rows that DO NOT contain the text string your specify, then delete them.
How Does It Work?
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 assumes our data set is set up in a certain way, with the first row containing field headers or column names. (Which mine is!).
Ok let’s get coding…….
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 Worksheet, 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 DOES NOT contain “Invoice”, 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!!
Do You Want To Download The Code?
.AutoFilterMode = False
With Range(“a1”, Range(“A” & Rows.Count).End(xlUp))
.AutoFilter 1, “<>Invoice”
On Error Resume Next
.AutoFilterMode = False