Processing Autofiltered Data with an Excel Macro

[IanThomas]

Hi Folks,

My problem at the moment is that I need to put values into filtered cells using a macro, but leave those that are not shown alone.

I am doing this at the moment by using a macro to auto-filter the data for a specific criteria, & then looping through each record (row) and getting excel to decide weather the data is filtered or not (i.e. IF Entirerow.Hidden=False then activecell.value = "Filtered Data"). This is OK with small datasets, but when I come to do more than 10,000 rows it takes ages. It even looks through each record to see whether it's hidden or not, even when I can see that All records have been hidden!! (e.g. '0 out of 10000 filtered' in the bottom left hand corner) ....so ....

I was wondering if anybody knows a quick way where I can change all of the visible cells in a column of filtered records without having to get Excel to select each one individually??

Any help would be MUCH appreciated.

Thanks in advance

Ian T

 

[VBAjedi]

I believe you need this:

Set MyRange = Selection.CurrentRegion.SpecialCells(xlCellTypeVisible)

That would assume you have a cell in your filtered data selected. You can use any range in place of Selection.CurrentRegion if that works better for you.

Now you can just loop through MyRange and do whatever it is you want to do. If you only want to work with one column, you can further refine MyRange with:

Set myrange = Intersect(Columns("A:A"), Selection.CurrentRegion.SpecialCells(xlCellTypeVisible))

Hope that helps!

VBAjedi

 

[IanThomas]

Thankyou VBAJedi!

That's Excellent! Just what I needed.

Ian T