Advanced Filter copy to range. columns rearranged

[nigelog]

Hi All

the code below takes column data and changes the display order via an autofilter criteria

This worked fine until the dataset increased. The filtering looks for true in a column and passes the "column sorted" data to an output report.

Can I change this method to Advanced Filter and consider only the visible cells, commit them to array and then change the sort order to output the array

Please advise if there are better alternatives

Please Login or Register  to view this content.

[Richard Buttrey]

Why not just use Autofilter and a SpecialCells instruction? e.g

Please Login or Register  to view this content.

You may need to create a variable to work out the column to which you want the filtered stuff to be copied. I've also assumed the CurrentRegion property will pick up just columns A:AU from row 5 down. If not you'l need to make a small modification.

[nigelog]

Hi Richard, its the different order of the columns using the Advanced Filter method that I cant get to grips with. I suppose I could filter in place and then try to sort the columns from an array

[Richard Buttrey]

To use an Advanced Filter where the columns are differently laid out or a subset of columns then create and name a dedicated output range say 'mydataout' that contains just the column labels you need and a named two cell criteria range - say 'crit' that contains your column J label in the first cell and TRUE in the cell below it.

Then the data filter is

Please Login or Register  to view this content.

Then copy the MyDataOut curret region to your Priority sheet

[nigelog]

Hi Richard, I tried in a test workbook and spot on, many thanks. I might have to hide a row with column labels in source sheet because of layout in headers but should be Ok

Ill add the test workbook in case it is of use to anyone else searching for this