Automating Advanced filtering

kannan14 · #1 07-17-2018, 12:20 AM

Hi everyone.
I am trying to automate Advanced filtering option. The first few lines gives the criteria and the below table is actual data.

The number of rows in filtering can change dynamically based on the inputs given (it can be a single line or two or three lines).

Advanced filtering option requires no blank row. So the criteria table size should get adjusted based on the number of rows of criteria. I am a new to macro programming. Could you please check and let me know where I am making mistake in the code.
Thanks and appreciate your help.

p45cal · #2 07-19-2018, 06:19 AM

Since you have the criteria range in its own 'white space'', you can use CurrentRegion to determine its extent, so you only need one line (you don't need the Dims because the code is inside the sheet's own code-module so all unqualified ranges refer definitively to that same sheet):

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B12:C24").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B3").CurrentRegion, Unique:=False
End Sub

p45cal · #3 07-27-2018, 04:19 AM

Delete one:

Thanks p45cal, that worked a treat.
That didn't work; what rubbish.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automating Updates For Advanced Filtering Into New Sheet	indieben	Excel	12	07-23-2018 04:08 PM
Need to put citations in, need help automating if possible	AOEUD	Word	2	11-06-2015 02:58 PM
Filtering (advanced?) in Pivot Table	canajun	Excel	6	01-17-2015 05:01 AM
automating powerpoint	trstbmbk	PowerPoint	1	11-12-2013 02:09 AM
Automating dates	OCM	Outlook	4	04-03-2013 08:29 PM