Automatically select Filter items

[Franck Knight]

I have a VBA macro on Excel I wrote at work to print some reports. I'm always looking into making more automatical steps, and I haven't found an example on how to do this yet.

The last step essentially has me manually select each item in the filter list of a column, one by one, printing each. The column is always the same, but the list's content will vary as it's based off unique invoice numbers.

I was wondering if there was an easy way to do this, something like For Each X in FilterList or Filterlist(X).select . It's probably not as simple, but that's why I'm here

Thanks in advance!

[JBeaucaire]

Actually, you got it on the head. I do this very thing frequently.

I use the ADV Filter to copy a UNIQUE VALUES list to another empty column, sort it, then filter the original column by the unique values in a loop. Very efficient.

Please Login or Register  to view this content.

[Franck Knight]

Hmmm.

Well this works fine for the purpose of printing, but I've ran into a small difference, wondering how I can get around it.

When I was doing it manually, it was keeping the total at the bottom, so I'd have a dynamic total of the currently selected filter item printed. But when using your macro that total disapears.

Maybe I need to space the total by one more row?

[JBeaucaire]

Maybe I need to space the total by one more row?

That would do it. The Autofilter filters the concurrent range of data, so that would fix it, I'd think.

[Franck Knight]

Adding a new line didn't fix it right away, but I found a work around.

I think Excel 2003 AutoFiltering system is a bit messed up. I tried adding a new line, but since the totals are in a different column (N-O in my case) than the filtering column (B), Excel was doing what it wanted and hiding it.

First I changed the AutoFilter range of your Macro from A:A to Rows(1:1).Autofilter. That's what my macro originally had and it woked for me with the total row. But yours specified columns instead, so Excel behaves differently for some reason, and the AdvancedFilter you apply at the begining of it remoevs what AutoFilter I had beforehand, so I have to place a new one.

Rows(1:1).Autofilter puts on every column that has data (which included your EE even though it was erased, so I modified it to a closer Q:Q in my case). After that, I modified that last Autofiltering line to be Range(A:O) and it seems to not hide the total row anymore.

Thanks for the help Hope this info will help similar problems as well.