Auto Filter processing all variables

[ianh]

I currently use some code in a project like this.

Please Login or Register  to view this content.

RegGroup is a list of known values that cell G1 steps through (using a different bit of code thats not important.)

What i would like to do is for the filter to step through all of the variables in the column rather than a specific filter value being passed to the filter.

The values will always be a text string and not a number. I'm guessing that i need the code to first test the column for each unique value and then step though each one as the criteria.

Any ideas?

[snb]

Please Login or Register  to view this content.

[ianh]

Thanks for the suggestion snb but i cannot create a list within the same sheet as done in your example.
Ideally i don't want to edit the data on/in sheet/workbook.

Ive attached and example of data. Filtering Example.xlsm

Often the task im given is to run a report (large data sets from an MIS), apply a filter, and print/PDF separate reports from the output for each of the variables in any given column.

Not sure if im now making more or less sense.

[snb]

Why not ?
You can remove those data afterwards.

Please Login or Register  to view this content.

[ianh]

the reports will always have a variable number of columns. Although (thinking while im typing) i suppose i could use something to determine the width of the reports first and set the Offset accordingly in your example.

When i run your example i get an error on the with line "subscript out of range". Is it because i have the name ranged set to the wrong area?

[snb]

yes determining the last column isn't a big deal.

Yes, it means "table6" doesn't exist.

[ianh]

I've checked in name manager and table6 is definitely in there with the range of data.
I've tried changing it to

Please Login or Register  to view this content.

and no joy either.

What am i missing?

[snb]

Don't use 'activesheet' but refer to the sheet, using it's name: sheets("sheetname") and if necessary make the reference to the workbook the worksheet belongs to.

[ianh]

thanks for your time and patience snb but i just cant get this to work with the example attached.
I'm clearly making an error but regardless of what i refference in that line i cant get it to execute as expected.

If possible could you amend the example i've attached in the previous post, your code sample is already in the module, it would be much appreciated.

[ianh]

snb, I've got my act together and finally got my head around it.

Please Login or Register  to view this content.

Is giving me what i need. I can amend it now to make it fit any of the projects i'm working on (columns and field becoming variables to).

Again thanks for any time you spent on this it was much appreciated.

[snb]

I would use this:

Please Login or Register  to view this content.

[ianh]

thanks again snb,

Your version seems to run quicker as well. not sure if that's just my imagination.