MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating a macro


Posted by Becca on November 20, 2000 1:52 PM

How do I create a macro in Excel that recursively deletes the entire row if column 'M' has a value <=0 throughout the entire document?
If anyone could help me I would greatly appreciative.

Thanks


Posted by Ben O. on November 20, 2000 2:15 PM

You could use a For-Until loop, many examples of which can be found on the Mr. Excel page, but applying the autofilter and simultaneously deleting the rows that match your criteria is faster (in my experience).

Try:

Range("A1:M2000").AutoFilter Field:=14, Criteria1:="Criteria1:="<=0"
Range("A2:M2000").ClearContents
Worksheets("Sheet1").AutoFilterMode = Off

You'll want to change the range to include all of your data. Make sure that the range you use ClearContents on starts one row beneath the range you use the AutoFilter on, since you don't want to delete your header row.

-Ben

Posted by Becca on November 21, 2000 8:34 AM


Ben,

It worked! Thank you for you terrific advice! I really appreciate it. :)

Becca


Posted by Becca on November 21, 2000 8:34 AM


Ben,

It worked! Thank you for you terrific advice! I really appreciate it. :)

Becca