Excel Autofilter VBA

[AppStaff]

I'm trying to automate from MS Access an excel spreadsheet and programmatically add an autofilter to the 3rd row.

The problem is every syntax I use puts it on the first row. Does anyone know how to code adding an autofilter to the 3rd row of an excel worksheet using VBA. Thanks much for any suggestions!

 

[Gavona]

If you do not want Excel to 'guess' the range to be filtered then you must be explicit and tell it the entire range to be filtered.

As always the best way to find out is to use the macro recorder when you do the task manually - then copy and adapt the code - post back with your code and any problems.

So switch on macro recorder. Highlight the range you want filtered and apply the autofilter.
As always the recorded code includes plenty of Select statements - get rid of them and you get something like

Range("A4:E15").AutoFilter

In practice you might want to go to A3 hold Ctrl, Shift and press Enter then apply the autofilter. The code from this can again be consolidated to a single line.

Hope this helps,


Gavin

 

[AppStaff]

Thanks very much Gavin. I was hoping I could have it guess given the range is variable and I'm too lazy to code more

It does work with a specific range as you suggested instead of just the range("A3") that I was trying.

Star awarded.

 

[Gavona]

Thanks for the star! Did you try the Ctrl-Shft-Enter thing? That addresses the variable range issue!

Regards,


Gavin