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?

---

Originally Posted by Bryan Hessey

another tuph one huh?
---

Hooray! At least, someone gets it!

Thanks for the tips. The macro can recreate the autofilter for each deletion, if necessary, but retaining it would be my first choice.

Originally Posted by tuph

Hooray! At least, someone gets it!

Thanks for the tips. The macro can recreate the autofilter for each deletion, if necessary, but retaining it would be my first choice.

Either

  Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

or


ilastrow = ActiveSheet.Range("A65536").End(xlUp).Row
With w2
   .Range(Rows(2), Rows(ilastrow)).Select
    Selection.Delete
    End With

should work, but don't use =Rank(~) column as your test data, it sends you funny.

hth
---