Hi everyone,
I've been looking a lot on this forum lately and decided to ask for help about a problem that's been bugging me for days (first time posting).
Thanks in advance! Seriously that would be a huge help... im completly stuck.
Here's the situation:
- 10 cell long X 20 row large excel table with occasionnal "A" , "B" or "C" in cells. Header is week# (10,11,12...20)
- I want to filter between any 2 weeks (exemple: all row with "B" between week "10" and "14") specified in 2 designated cells (ex: Start = G1 and End= G2)
. Here it should show row 2, 3, 4, 7
[TABLE="width: 500"]
A
B
C
D
E
1
10
11
12
13
14
2
A
B
3
A
B
4
A
B
5
A
C
6
7
B
C
8
[/TABLE]
Here's the strategy used (but not enough) :
- In another sheet, make 3 times (1 for each letter "A", "B", "C", 10X10 cell criteria list with header week# (10,11,12...20), in the format of 1 criteria per row
ex:
[TABLE="width: 500"]
A
B
C
D
E
1
10
11
12
13
14
2
B
3
B
4
B
5
B
6
B
[/TABLE]
- Use function to convert week# to cell position
- Use variable to store start (s1) and end (s2) position as number
- Use the advanced filter macro
Here's the problem
- I get some empty row when i filter. For exemple if i want week 11 to 13 (B1 to D6), i will have emtpy criteria row and filter wont work correctly because he will take "blank" as a criteria, which i dont want.
- Having the dynamic range cause me a lot more problem than i though. I tried something like:
test_range = Range(Cells(1, s1), Cells(5, s2)) but it doesnt work...
Here's the code
Added to the blank row, i cant get the dynamic part working well either. I tried something like this:
Dim test_range As RangeDim s1 as integerDim s2 as integers1 = range("Z10") 'just consider its correct value for nowS2 = range("Z11") 'just consider its correct value for nowSet test_range = Range(Cells(1, s1), Cells(6, s2)) 'here 1 is the first row and 6 is the last row of the criteria tableRange("A1:E7").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _Range(test_range), Unique:=FalseSo to resume, i need help on how to get past empty row problem and dynamic colomn selection. Would be very appreciated!
