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

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you get an actual error message when the code runs?

If so, what is the full message and what line of code is highlighted when you click "Debug"?

 

[paulrh]

The error is:

Compile error:

Expected Function or variable


The word "ActiveCell" in the code is highlighted.

Thanks, Paul

 

[Peter_SSs]

I have not been able to reproduce the error.

Have you posted your full code?

What is the significance of range A2:Z12? Do you only have data down to row 12 or less?

 

[paulrh]

Sent you a PM.

Range A2:Z12 is currently where i have data for testing purposes.

 

[Peter_SSs]

Paul

Having seen you actual file, I make the following comments, some specific to your circumstance and some general.

1. Filtering on numeric values can be a little bit trickier than text values.

2. A bit odd that you were trying to filter A2:Z12 when your data extends beyond row 12.

3. You rarely need to select anything to work with it in vba and selecting slows your code.

4. There is no real need to use ActiveSheet, since that is the sheet the button is on so must be active when clicked.

5. Your current version of Excel has more than 65,536 rows. Whilst it is no doubt is unimportant for this exercise, I would say that it is good practice to always use the structure I have shown below which will check up from the bottom of the sheet whether it is an older one with 65,536 rows or a newer one with 1,048,576 rows.

So, try this

Private Sub CommandButton3_Click()
    Dim sCrit As String
    
    sCrit = Range("A" & Rows.Count).End(xlUp).Value
    Range("A2:Z" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=sCrit
End Sub

 

[paulrh]

Peter,

SOLVED - this works fantastic - exactly as required.

If you have time could I trouble you for one more step. After AutoFiltering on the last entry would it be possible to sort the data by date (column C) with the newest record at the top?

I think this involves an array so way beyond my level of adaptation of code I find in these forums.

Many thanks.

 

[Peter_SSs]

Peter,

SOLVED - this works fantastic - exactly as required.

If you have time could I trouble you for one more step. After AutoFiltering on the last entry would it be possible to sort the data by date (column C) with the newest record at the top?

I think this involves an array so way beyond my level of adaptation of code I find in these forums.

Many thanks.

Does this do what you want?

Private Sub CommandButton3_Click()
    Dim sCrit As String
    
    ActiveSheet.AutoFilterMode = False
    sCrit = Range("A" & Rows.Count).End(xlUp).Value
    With Range("A2:Z" & Range("A" & Rows.Count).End(xlUp).Row)
      .AutoFilter Field:=1, Criteria1:=sCrit
      .Sort Key1:=.Cells(2, 3), Order1:=xlDescending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
End Sub

 

[paulrh]

Yes, this is superb. Thank you so much for your help. Awesome

 

[Peter_SSs]

Yes, this is superb. Thank you so much for your help. Awesome

You are welcome.