Macro for deleting autofiltered rows

Originally Posted by tuph

Having decided that I can't ignore macros and VBA any longer if I want to progress my Excel skills, I have recorded a macro today for the following scenario.

I receive a raw data file from the mainframe which I open in Excel and save and a worksheet. Here's a sample:


My macro first inserts column A and adds a formula to enable me to easily identify cash customers. Next, it creates an autofilter on row 3, which contains the column headings, then filters by column A to delete all rows where column A = "A9"; then filters by column D and deletes a variety of category codes.

All of this is works fine on the current data file, but when I look at the macro code it refers to specific row numbers in the delete portions. I want to use this macro every month, but the row numbers in each category can change because the number of customers will (hopefully!) increase. Is there any way to make the macro delete everything selected by the autofilter without specifically referring to row numbers?

Hi,

another tuph one huh?

something like

Set w2 = Worksheets("Sheet1")
w2.Rows.Select
Selection.Delete

removes those rows, but removes the filter also.

I will test rows 2:lastrow to see if that helps, I presume that you want to retain the filtter?

---