Macro for deleting autofiltered rows

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:

PREFIX CUST.CODE BRANCH CAT DATE OPENED LAST INV.DATE
A1 A1ENTE 12 8 24/04/06 14/12/06 10,095
A1 A1TAS 85 8 12/05/05 13/12/06 4,890
A8 A80132 74 8 11/09/02 0
AA AAA24 29 8 1/01/86 13/12/06 5,812
AA AAAANO 58 8 27/07/95 22/09/06 1,239

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?

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

hth
---