Help with CriteriaRange in VBA .AdvancedFilter Method. ( calling in the Cavalry !! )

[DocAElstein]

. Hi senior “Colleagues”!,
.
. One way I am learning my Excel / VBA is by answering Threads here.
. I answered one with both a Formula and a VBA Code. The code was much too complicated As I was not yet familiar enough with the method I expect is the best, the VBA Advanced Filter Method. Particularly I do not have the experience yet in getting the correct CriteriaRange

. can someone help?

. So, a simplified example.. I start with this.

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Date[/td][td]startdate[/td][td]stopdate[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

3104​

[/td][td]4/18/2015[/td][td]4/21/2015[/td][td]4/25/2015[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

3599​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

4004​

[/td][td]4/15/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

7158​

[/td][td]4/25/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]JL DataAdvFilt[/td][/tr][/table]

……………
. And wot I finally want is this.

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Date[/td][td]startdate[/td][td]stopdate[/td][td][/td][td]Code[/td][/tr]

[tr][td]

2​

[/td][td]

3104​

[/td][td]4/18/2015[/td][td]4/21/2015[/td][td]4/25/2015[/td][td][/td][td]3599[/td][/tr]

[tr][td]

3​

[/td][td]

3599​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td]7158[/td][/tr]

[tr][td]

4​

[/td][td]

4004​

[/td][td]4/15/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

7158​

[/td][td]4/25/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]JL DataAdvFilt[/td][/tr][/table]

.

. Putting it into words.

. I want to list out the Unique Codes Numbers from Column A, but only if the corresponding date or dates for any of any these Code Numbers ( given in the corresponding row in Column B ) lie within the start and stop date criteria given in cells C2 and D2 respectively.

. I have one possible start point in the code I give a the end. There, in line 70, the Advanced Filter is run first without any CriteriaRange, with unique argument set to true,
Unique:=True
. … this gives me a column of the unique Code numbers which I place arbitrarily starting in cell C10.

. I expect the next step would be to complete the missing bit in my argument for the critical Range,
CriteriaRange:=Range("C10:________,
. …. To run the Advanced Filter a second time In my line 90.
. In addition I would need the appropriate combination of headings, and or not headings in a the critical range which has cell C10 as its Top left corner. Also I need the exact format / syntax that I put in a few rows starting at row 10 and being within the specified Critical Range.

. Possibly some other Critical Range could do away with my code line 70 ?

…………………………………………..

. I have a non AdvancedFilterMethod VBA code, and I would like a VBA AdvancedFilterMethod version. But of course if anyone “feels the urge to do some other VBA Code or a better formula then me then please do so here or in that Thread
http://www.mrexcel.com/forum/excel-...ique-values-depending-date-adjacent-cell.html

. Any response here which I use I will, of course, a-credit the worthy!!

Many Thanks
. Alan
……………………………………

Here my start attempt at the Advanced Filter Method VBA code

[color=blue]Sub[/color] AdvancedFilterMethod()
10 [color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("JL data") [color=lightgreen]'[/color]
20
30 [color=blue]Dim[/color] lastrow [color=blue]As[/color] Long: [color=blue]Let[/color] lastrow = wks1.Cells(Rows.Count, "A").End(xlUp).Row
40 [color=lightgreen]'[/color]
50 [color=blue]Dim[/color] rngCode [color=blue]As[/color] Range: [color=blue]Set[/color] rng = wks1.Range("A1:A" & lastrow & "")
60 [color=lightgreen]'AdvancedFilter Run 1 to get Unique Codes for critical range in Run 2[/color]
70 rngCode.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wks1.Range("C10"), Unique:=[color=blue]True[/color]
80 [color=lightgreen]'AdvancedFilter Run 2[/color]
90 rngCode.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("C10:________, CopyToRange:=wks1.Range("F2")
 
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'AdvancedFilterMethod()[/color]

In case it helps, particularly as getting the correct critical range criteria across in a screenshot can be tricky I enclose the File I am playing with just now , should you have the time to view, and possible put a solution in for me.. I will paste appropriate screen shots so everyone can follow anything you do..
https://app.box.com/s/kffotse2fsqoifavx2w3whpra6w4jjqd

 

[DocAElstein]

Hi,
. Sorry to BUMP . I do not favour Bumping myself. But I have waited a bit, ( and busily answered a few Bumpers myself in the meantime!. )
. I expect this could be an easy one if it catches anyone familiar with getting the correct Critical Range criteria using the VBA Advanced Filter.

. Any chance? ( No rush )

Thanks
Alan

 

[hiker95]

DocAElstein,

if anyone “feels the urge to do some other VBA Code

Here is a macro solution using the Scripting.Dictionary for you to consider, based on the raw data, and, results, that you have displayed.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E	F
1	Code	Date	startdate	stopdate
2	3104	4/18/2015	4/21/2015	4/25/2015
3	3599	4/23/2015
4	4004	4/15/2015
5	7158	4/25/2015
6	7158	4/23/2015
7	7158	4/23/2015
8
JL DataAdvFilt

After the macro:

Excel 2007
	A	B	C	D	E	F
1	Code	Date	startdate	stopdate		Code
2	3104	4/18/2015	4/21/2015	4/25/2015		3599
3	3599	4/23/2015				7158
4	4004	4/15/2015
5	7158	4/25/2015
6	7158	4/23/2015
7	7158	4/23/2015
8
JL DataAdvFilt

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ExtractUniqueCodesBetweenDates()
' hiker95, 05/31/2015, ME857793
Dim rng As Range, c As Range, o As Variant
With Sheets("JL DataAdvFilt")   '<-- you can change the sheet name here
  Set rng = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
  With CreateObject("Scripting.Dictionary")
    For Each c In rng
      If c.Value >= [C2] And c.Value <= [D2] Then
        If Not .Exists(c.Offset(, -1).Value) Then
          .Add c.Offset(, -1).Value, c.Offset(, -1).Value
        End If
      End If
    Next c
    o = Application.Transpose(Array(.Keys))
  End With
  .Columns(6).ClearContents
  .Cells(1, 6) = "Code"
  .Cells(2, 6).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(6).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ExtractUniqueCodesBetweenDates macro.

 

[RoryA]

Label both criteria columns 'date' and use >= and <= for the relevant date values. Then the criteria range is just those 4 cells.

 

[DocAElstein]

Label both criteria columns 'date' and use >= and <= for the relevant date values. Then the criteria range is just those 4 cells.

Hi Rory,
. Thanks for the reply.
. I had tried previously variations along the lines you suggested without success...
. But since your reply I have tried again..............
.
. ......... And have some very peculiar results.
. I will do my best to summarize:
.
. I start with any New Sheet, and prepare a Criteria range along the lines that you suggest:
.
.Spreadsheet screen shot 1
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Date[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

3104​

[/td][td]4/18/2015[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

3599​

[/td][td]4/23/2015[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

4004​

[/td][td]4/15/2015[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

7158​

[/td][td]4/25/2015[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td]Date[/td][td]Date[/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]>=4/21/2015[/td][td]<=4/25/2015[/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]NewSheet1[/td][/tr][/table]

. I prepare the following code, along the lines of my original ( which just gives me unique Code Numbers ). Initially the modified code has the new lines 82 – 84 commented out

[color=blue]Sub[/color]  AdvancedFilterMethodRory()
10  [color=blue]Dim[/color]  wks1  [color=blue]As[/color]  Worksheet:  [color=blue]Set[/color]  wks1  =  ThisWorkbook.Worksheets("NewSheet1")  [color=lightgreen]'[/color]
20
30  [color=blue]Dim[/color]  lastrow  [color=blue]As[/color]  Long:  [color=blue]Let[/color]  lastrow  =  wks1.Cells(Rows.Count,  "A").End(xlUp).Row
40  [color=lightgreen]'[/color]
50  'AdvancedFilter  Run  1  to  get  a  Unique  Codes  Column
60  [color=blue]Dim[/color]  rngCode  [color=blue]As[/color]  Range:  [color=blue]Set[/color]  rngCode  =  wks1.Range("A1:A"  &  lastrow  &  "")
70  rngCode.AdvancedFilter  Action:=xlFilterCopy,  CopyToRange:=wks1.Range("C10"),  Unique:=[color=blue]True[/color]
80  [color=lightgreen]'AdvancedFilter  Run  Rory2[/color]
[color=lightgreen]'82  Dim  rngCodeDate  As  Range:  Set  rngCodeDate  =  wks1.Range("A1:B"  &  lastrow  &  "")[/color]
[color=lightgreen]'84  rngCodeDate.AdvancedFilter  Action:=xlFilterCopy,  CriteriaRange:=wks1.Range("C8:D9"),  CopyToRange:=wks1.Range("D10")  ',  Unique:=True[/color]
 
[color=lightgreen]'90  rngCode.AdvancedFilter  Action:=xlFilterCopy,  CriteriaRange:=Range("C10:________,  CopyToRange:=wks1.Range("F2")[/color]
 
 
[color=blue]End[/color]  [color=blue]Sub[/color]  [color=lightgreen]'AdvancedFilterMethodRory()[/color]

.
. I run the code , and obtain the similar results as previously

Spreadsheet Screen Shot 2


Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Date[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

3104​

[/td][td]4/18/2015[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

3599​

[/td][td]4/23/2015[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

4004​

[/td][td]4/15/2015[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

7158​

[/td][td]4/25/2015[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td]Date[/td][td]Date[/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]>=4/21/2015[/td][td]<=4/25/2015[/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td]Code[/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td]

3104​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td]

3599​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td]

4004​

[/td][td][/td][/tr]

[tr][td]

14​

[/td][td][/td][td][/td][td]

7158​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]NewSheet1[/td][/tr][/table]

.. I uncomment out the new lines and attempt a run , ( initially having the spreadsheet as in first Spreadsheet screen shot 1 )
.
. I obtain the following results..

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Date[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

3104​

[/td][td]4/18/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

3599​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

4004​

[/td][td]4/15/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

7158​

[/td][td]4/25/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td]Date[/td][td]Date[/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]>=4/21/2015[/td][td]<=4/25/2015[/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td]Code[/td][td]Code[/td][td]Date[/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td]

3104​

[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td]

3599​

[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td]

4004​

[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td][/td][td][/td][td]

7158​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]NewSheet1[/td][/tr][/table]

. I attempt another run of the program, ( same start point again as in screens shot 1 )
.
. I obtain the following results


Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Date[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

3104​

[/td][td]4/18/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

3599​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

4004​

[/td][td]4/15/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

7158​

[/td][td]4/25/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

7158​

[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td]Date[/td][td]Date[/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]>=4/21/2015[/td][td]<=4/25/2015[/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td]Code[/td][td]Code[/td][td]Date[/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]NewSheet1[/td][/tr][/table]
.
.
. Sometimes deleting the new criteria range results in my original code working, but not always.
. It appears that any attempt at those extra code lines puts some strange spanner in the works when attempting to use the .AdvancedFilter Method further. Very strange
. And check this out. If I have my initial code working, but then paste in the new criteria range and run the code with the new lines commented out... then my original code does not work!!!!
. I have one sheet, for example now, where all criteria ranges etc. And any code references to the new code line attempts i have deleted. Still my code will no longer work since i made attempts to get the new code lines to work!!!
. Clearly the presence of those criteria in the spreadsheet does something weird!! –Which is sometimes remembered in the sheet!!

. Any Ideas?
. ( I do not get any errors ... so cannot attempt any error handling!! )

Alan

 

[RoryA]

What are your regional date settings?

If the entries in column B are stored as real dates, the code should work with the layout you have, but the criteria dates must be entered in US format. It would be safer to use:
=">="&DATE(2015,4,21)
=">="&DATE(2015,4,25)
in the two criteria cells.

 

[hiker95]

DocAElstein,

if anyone “feels the urge to do some other VBA Code

Have you tried my macro solution?

 

[DocAElstein]

Hi Hiker,
. Hope the fishing went well..

...

Have you tried my macro solution?

Of course, as always - Brilliant Hiker. I am very grateful.
. I was preparing an answer to say thanks and wanting to give some interesting feedback, but I have got tied up in the .Advanced Filter problem.
. I need to concentrate on that now, but hope to give you some interesting feedback later
. Thanks again
Alan

 

[DocAElstein]

Hi Rory,
. Thanks again

What are your regional date settings?

If the entries in column B are stored as real dates, the code should work with the layout you have, but the criteria dates must be entered in US format...........

. my regional settings , I guess , are European, but I have always been surprised that I have never experienced any problems with date formats regardless of how they “look” in the sheet. I have always thought that by any evaluation < > = etc, the underlying number for date ( that number that starts at 1 around the date 1900 ) is used. I have done codes involving date comparison using files from Ops from many lands and the date comparison was never an issue, even when what they “saw” and I “saw” on the spreadsheet was different.
. I never experienced any problem with any other VBA codes.. For example any codes other than those using .AdvancedFilter method ( including the latest from Hiker ) which I gave the OP for this requirement. They all worked fine... and they still do - even on my now “ Infected” sheets!!!

..........It would be safer to use:
=">="&DATE(2015,4,21)
=">="&DATE(2015,4,25)
in the two criteria cells.

. I tried your new date criteria. Same results again. – Starting with a new sheet, my first rngCode.AdvancedFilter part works initially , the rngCodeDate.AdvancedFilter does not. After the first run, my rngCode.AdvancedFilter no, longer works. Sometimes my rngCode.AdvancedFilter will then work if I delete the new criteria Range("C8:D9")
.
. Appears to be some 2 issues.
. a) Not getting the rngCodeDate.AdvancedFilter to work,
. b) An attempt at it usually stops any further AdvancedFilter working in that sheet. A real Puzzler. For this second problem could there be some “ re-setting the exceptionally crashed .AdvancedFilter Method” ? - Like
On Crashed .AdvancedFilter goto -1
- of course what strange there is that the exceptionally crashed .AdvancedFilter Method” is not reset when I stop and re-run the code??
??

Alan

 

[RoryA]

There is nothing to reset. I'm pretty sure the dates in your example are stored as text - you need to convert them to proper dates to get anything to work reliably.