Filter by Select All

[VbaHell]

Hello All

I hope you can help on this please

I have a drop down list in cell "P2" which allows me to select a value that then triggers a filter with the code shown below.

Question 1 - How can I make this range Dynamic as the rows change daily.
Question 2 - It is possible to put a value in "P2" that causes the filter to "Select All"

 Sub filterProject()
 
ActiveSheet.Range("$A$6:$AC$1000").AutoFilter Field:=15, Criteria1:=ActiveSheet.Range("P2").Value
End Sub

 

[Trevor G]

Question 1 use currentregion.

Sub filter1A()
ActiveSheet.Range("A2").CurrentRegion.AutoFilter ' Then your criteria

End Sub

Question 2 - You could add an IF Statement into your Sub routine.

 

[jim may]

Sub SelectAll()
'To Select All records in the dropdown just enter "*" (Without the Quotes)


   ActiveSheet.Range("$A$6:$AC" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Range("P2").Value, Operator:=xlAnd
End Sub

Not sure, but you might want to consider using the above in a Worksheet Event - say Worksheet_Change_ based on the Change of Cell P2 Alone...

 

[VbaHell]

Thank you both for your replies, Jim I never thought of doing this with your range on the last row, very nice, I am also already using this with a worksheet change event

many thanks