Pass Autofilter Criteria to Variable 2

[idbr]

Hi,

I need to pick up the criteria of an autofilter and pass this to a variable. I also need to get the column being filtered.

The point is that I have a spreadsheet that allows the user to filter for the items they're interested in and subtotal(9,[Range])'s the values based on the filter. I want to print the selection summary from a command button and paste in the selection criteria as a title.

I can't just use a cell reference as they may be filtering on any one of four columns:

Sample data:

Sale Date Seller Contract Position

20-Dec-02 BBU BBU01 BBU20084
20-Dec-02 BBU BBU01 BBU20085
20-Dec-02 BBU BBU01 BBU20086
20-Dec-02 BCU BCU01 BCU20080
20-Dec-02 BCU BCU01 BCU20081
20-Dec-02 BCU BCU01 BCU20090
20-Dec-02 BCU BCU01 BCU20091
20-Dec-02 BCU BCU01 BCU20092
20-Dec-02 MBU MBU04 MBU20094
20-Dec-02 MBU MBU04 MBU20095

So, my final report header would look something like:

Deal Report: [Column Title] = [Filter Criteria]

I'm running XL2002 on an XP machine.

Thanks, Iain

 

[CautionMP]

Here is a snipit that should get you going in the right direction. I fired this from the VBA pane with the active cell anywhere in the filtered data and the result was:
[tt]Seller=BBU Contact=BBU01[/tt]

Public Function AutotfilterCriteria() As String
Dim rngFilter As Range
Dim objFilter As Filter
Dim lngColumn As Long
Set rngFilter = ActiveCell.CurrentRegion
lngColumn = 1
For Each objFilter In rngFilter.Worksheet.AutoFilter.Filters
  If objFilter.On Then
    AutotfilterCriteria = AutotfilterCriteria & _
                          rngFilter.Cells(1, lngColumn) & _
                          objFilter.Criteria1 & " "
  End If
  lngColumn = lngColumn + 1
Next objFilter
AutotfilterCriteria = Trim(AutotfilterCriteria)
Debug.Print AutotfilterCriteria
End Function

Hope this helps,
CMP

Instant programmer, just add coffee.

 

[Gavona]

That's a really useful bit of code, but how can I modify it to deal with a second criteria in a single column (eg seller = BBU or Seller = BCU)?

I modified two lines to make it a worksheet function:

Public Function AutotfilterCriteria(FirstTitleCell As Range) As String
Set rngFilter = FirstTitleCell.CurrentRegion

but found that I had to point to the top left cell in the filtered range to get the correct results - not any cell as you suggest. Not a major issue by any means.


Gavin

 

[CautionMP]

Criteria2

...
  If objFilter.On Then
    AutotfilterCriteria = AutotfilterCriteria & _
                          rngFilter.Cells(1, lngColumn) & _
                          objFilter.Criteria1 & " "
    If objFilter.Criteria2 <> "" Then
      AutotfilterCriteria = AutotfilterCriteria & _
                            "OR " & objFilter.Criteria2 & " "
    End If
  End If
...

I probably had the top left cell as the active cell when I was testing, sorry about the mislead.

CMP

Instant programmer, just add coffee.

 

[Gavona]

Excellent!


Gavin