I am working on a timetable which has several groups of tasks (e.g. month-end, budget etc).
users want to see only those groups of tasks that concern them. To do that, I have a form in the first tab where the users
select the groups of tasks that they want to see. Then they run a macro with advanced filter and see the filtered timetable
in the next tab. The macro I recorded is very simple:
Sheets("Timetable").Range("$A$17:$V$85").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
However the filtering does not work the way I want it to. The problem is (I think) that I have to tell Excel the
precise size of the criteria range for the filtering to work correctly.
However in my case the number of rows in
the criteria field is not fixed. It changes depending on how many groups of tasks the users will select (up to 15). I do not
know how to change the macro so that would provide under "criteria" the exact criteria range (or at least the
number of rows in that range) that will results from the selection of users (if you look into the file, the range can be
anything between D5 and D5:D18). In the file you'll see how as a workaround to using VBA I tried to define name
"criteria" as formula that calculates the address of the criteria range but of course it does not work
suppose this is not a problem for someone who knows VBA so I thought I'll ask the experts. The attachment will hopefully give
some idea about what I'm trying to do.
I will be very grateful for any help, also if there is a solution that does not require VBA
(And if I could get help over this weekend, it would be really great!)