Filter by Select All

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
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"

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

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
Rich (BB code):
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...
 
Last edited:

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,024
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top