Sort a Filtered List

[yawnzzzz]

Hi,

I have a list that a user will filter, and then push a button for my VBA coding to run. The VBA then loops through the unfiltered rows. The problem I've run into is that there's so much data that Excel doesn't like the amount of noncontiguous rows.

My hope is that I can sort this list, so the data is together. The only problem is that it appears I have to unfilter the list in order for it to sort correctly. Does anyone have any coding that will record all the criteria for a filtered list, so I can then unfilter it, sort it, and reapply the filters?

[AlphaFrog]

There may be other workarounds. Can you better explain exactly what you mean by "Excel doesn't like the amount of noncontiguous rows"?

Excel can handle 8,192 areas in a range object. If you have more, you may be able to process them in sub-blocks within a loop. This may be easier than unfiltering-sorting-filtering.

[yawnzzzz]

The issue is this particular Excel file has a large amount of data in it (100,000+ lines). I needed to loop through all of the unhidden rows to perform an action in VBA, and the SpecialCells(xlcelltypehidden) function would error out. I ended-up coding a recursive loop to keep splitting the range into multiple sections until it didn't error out anymore.

Even though I have a solution to my problem, I would still be curious if anyone has written coding that could unfilter and refilter based upon the same criteria, since this coding could be reused in many different scenarios.