Microsoft Office Tutorials and References
In Depth Information
DELETE RECORDS IN VBA
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:
Dim rng As Range
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
‘ While the headings are in row 1, this range start in row 2
Set rng = Cells(2, 1).Resize(FinalRow - 1, 1)
‘ Apply a ﬁ lter to the dataset
Cells(1, 1).AutoFilter ﬁ eld:=4, Criteria1:="S29"
‘ Delete the visible cells starting in row 2
‘ Turn of the ﬁ lter
The macro ﬁ rst ﬁ nds how many rows are in the data set. It then deﬁ nes 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.
Figure 148. One line of code turns on the AutoFilter
dropdowns and chooses S29 from the sales rep dropdown.