Filter by Selection

[Yamezz]

Can anyone point me in the right direction to write a macro that filters my table by the values currently selected?

Currently, to filter my data I have to click the filter arrow on ColumnA, un-select the "select all" box, then scroll through the list to select the value I want to use in the filter. I then move on to do the same for ColumnB, ColumnC and ColumnD. Doing this all day gets tedious, so I want to be able to just use the mouse to highlight a selection in a single row and use the selection as the inputs for the filter.

i.e. highlight W,X,Y,Z in a single row, press a macro button and the VBA code filters my data for W in ColumnA, X in ColumnB, Y in ColumnC and Z in ColumnD.

Thanks.

 

[davesexcel]

Sub Button1_Click()
    Dim c As Range, x
    x = 1
    For Each c In Selection
        Range("A1").AutoFilter Field:=x, Criteria1:=c
        x = x + 1
    Next c

End Sub

 

[Yamezz]

Thanks Dave, that's really handy. So handy in fact, I'd like to use it in other worksheets, where the columns I want to filter are not always columns A, B, C and D.

How would you get the code to see what cells are selected and apply the filter to the actual columns that the selection uses? For example I could have data a single row in columns F and G to use as the filter criteria; then next time I may just want to filter using the value I've selected in just column E.

So instead of writing an individual macro for each scenario, I could have one global (is that the right term) macro that can be called in any worksheet to filter the data by my selection, in whichever columns that happens to be.

 

[davesexcel]

Yes, there are just too many variables to work for one macro to work with everything.

 

[Yamezz]

Could you not get Excel to:

Get column number for the left most cell in the selection
Filter that column by the value in that cell of the selection
Move to next cell in selection and repeat until end of selection

 

[Peter_SSs]

This has no checks that you might have multiple rows selected or cells selected outside the used range, but see if it is any use to you.
So it relies on the user having a "sensible" selection.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Filter_Selection()<br>  <SPAN style="color:#00007F">Dim</SPAN> fc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>  <br>  <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>    .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> .UsedRange<br>      fc = .Column<br>      cols = .Columns.Count<br>      <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection<br>        .AutoFilter Field:=c.Column - fc + 1, Criteria1:=c.Value<br>      <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Yamezz]

Thanks very much Peter. That seems it will work, the only thing is my filtering headings are on Row2. Row1 I use to display SUBTOTAL values of what I'm filtering. Can the code take this into consideration? Also, what's the reasoning behind turning the autofilter off first? Cheers.

 

[Peter_SSs]

.. the only thing is my filtering headings are on Row2. Row1 I use to display SUBTOTAL values of what I'm filtering. Can the code take this into consideration?

Try adding the blue code

With .UsedRange.Offset(1)

Also, what's the reasoning behind turning the autofilter off first?

To stop the code failing (error) if another part of the sheet happened to already be filtered when the code was run

 

[Yamezz]

Excellent Peter. Thanks very much for your help.

 

[Peter_SSs]

Had some extra code in there I forgot to take out as I tweaked the code. Don't need the 'cols' part. So

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Filter_Selection()<br>  <SPAN style="color:#00007F">Dim</SPAN> fc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>  <br>  <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>    .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> .UsedRange.Offset(1)<br>      fc = .Column<br>      <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection<br>        .AutoFilter Field:=c.Column - fc + 1, Criteria1:=c.Value<br>      <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>