Delete Records In Vba

June 25, 2021 - by Bill Jelen

Challenge: You need to delete records that match a certain criterion, and you’d like to do it by using a VBA macro.

Solution: The typical solution involves running a For…Next loop backward, from the last row up to row 1, checking each record and deciding whether that record should be deleted. The code to delete all the records with S29 in column D would be something like this:

Richard Schollar offered code that achieves this task quickly and efficiently by using the Excel AutoFilter command to isolate the desired records and then the SpecialCells property to delete only the visible cells.

For a 25,000-row data set, Excel can delete the matching records by running three commands instead of running through a loop and executing an IF statement 25,000 times. The code for this macro is:

The macro first finds how many rows are in the data set. It then defines an object variable that ignores the headings in row 1, starts in A2, and extends down through the data.

After you run the AutoFilter command, only the S29 records are visible, as shown in Figure 148.

In the next line of code, the rng variable points to A2:A999. Using SpecialC ells (xlCellTypeVisible) is equivalent to clicking the Special button on the GoTo dialog and choosing Visible Cells Only. This selects A9, A116, A134, and so on. In the same line of code, the EntireRow.Delete method removes all the rows that match the criterion.

The last bit of cleanup is to get rid of the AutoFilter dropdowns. You can issue the AutoFilter command without any arguments, as shown in the last line of code, to turn them off.

Summary: Using AutoFilter is a fast way to delete records that match a criterion.

Source: condensing this VBA on the MrExcel Message Board.

The code was proposed by Richard Schollar and nominated by Jon von der Heyden.

Title Photo: David Pennington on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.