Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active Cell to be used as filter...
Hi guys, think its a tough one...
I need to run a macro that filters data based on the contents of the active cell. Eg within my "info" sheet, I have: a1 = cat b1 = dog c1 = Mouse In another sheet (master) there are columns, one of which is "type of animal" If user is on cell a1 in the "info" sheet and activates the macro, I need the Macro to filter the column "type of animal" in the "master" sheet. (I also need to apply a second filter to another column in the master sheet, but that is not dependent on the users choice so I have been able to do using .AutoFilter Field:=13, Criteria1:="Y") Appreciate any thoughts,suggestions and solutions for this novice VBA user!!! :) Thanks Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active Cell to be used as filter...
Darin,
Of course, this depends on the location and layout of your datatable on sheet Master (specifically, which column has the Tyupe of Animal field): With Worksheets("Master").Range("A1").CurrentRegion .AutoFilter Field:=2, Criteria1:=ActiveCell.Value .AutoFilter Field:=13, Criteria1:="Y" End With -- HTH, Bernie MS Excel MVP "Darin Kramer" wrote in message ... Hi guys, think its a tough one... I need to run a macro that filters data based on the contents of the active cell. Eg within my "info" sheet, I have: a1 = cat b1 = dog c1 = Mouse In another sheet (master) there are columns, one of which is "type of animal" If user is on cell a1 in the "info" sheet and activates the macro, I need the Macro to filter the column "type of animal" in the "master" sheet. (I also need to apply a second filter to another column in the master sheet, but that is not dependent on the users choice so I have been able to do using .AutoFilter Field:=13, Criteria1:="Y") Appreciate any thoughts,suggestions and solutions for this novice VBA user!!! :) Thanks Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active Cell to be used as filter...
hmm..Thanks Bernie for replying - appreciate it. Perhaps my explanation was not good enough - what im trying to do is to put the active cell reference in the Autofilter field column, (not the criteria column) ie I dont know which field (1,2,3 or 4) to run the criteria (Y/N) on... (within master sheet I have 4 "type" columns - Type - Cat is 1st column, Type mouse is second col etc etc. Within the column I have a Y or a N. The reason for 4 columns and not 1 is that you could have an entry that releates to 1,2,3 or 4 columns (perhaps imagine owners as details on the rows) Thus when a user clicks elsewhere, I want excel to "choose" which column to apply the autofilter to. Perhaps named ranges would work...? Kind Regards Darin Darin, Of course, this depends on the location and layout of your datatable on sheet Master (specifically, which column has the Tyupe of Animal field): With Worksheets("Master").Range("A1").CurrentRegion .AutoFilter Field:=2, Criteria1:=ActiveCell.Value .AutoFilter Field:=13, Criteria1:="Y" End With -- HTH, Bernie MS Excel MVP *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
Hide Raws even the Auto Filter is active | Excel Worksheet Functions | |||
Open excel with a filter active an a certain value | Excel Discussion (Misc queries) | |||
Active Cell in Auto Filter | Excel Discussion (Misc queries) | |||
Filter range of only active cells??? | Excel Worksheet Functions |