Autofilter on Active Cell Content

[paulrh]

I require a Macro that uses the contents of the current Active Cell as the criteria for the AutoFilter. The criteria will always be the last entry in column A.

I have trawled though every possible solution I can find for the solution but with no success. I do not know why the following does not work. The "ActiveCell" always gets highlighted when the macro fails to run.

Private Sub CommandButton3_Click()
'Select last data entry in column 1
Range("A65536").End(xlUp).Select
' Filteronactivecell Macro
ActiveSheet.Range("$A$2:$Z$12").AutoFilter Field:=1, Criteria1:=ActiveCell.Value
End Sub


I am sure the answer is simple

 

[paulrh]

A really useful feature for my (any) spreadsheet would be to have a macro that filters on the active (single) cell.

I know this feature is available as a AutoFilter Command in Excel but for my target audience it would be too difficult to get them all to customize their ribbon. To have the macro available on the worksheet would be preferable.

I guess this is a little trickier as now both the Field and Criteria are variables.

 

[Peter_SSs]

A really useful feature for my (any) spreadsheet would be to have a macro that filters on the active (single) cell.

I know this feature is available as a AutoFilter Command in Excel but for my target audience it would be too difficult to get them all to customize their ribbon.

No need to customise the ribbons. Right click the cell and choose
Filter -> Filter by Selected Cell's Value