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


. 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

[size=4]-[CENTER]A[/CENTER][CENTER]B[/CENTER][CENTER]C[/CENTER][CENTER]D[/CENTER][CENTER]E[/CENTER][CENTER]F[/CENTER]
[CENTER]1[/CENTER]CodeDatestartdatestopdate
[CENTER]2[/CENTER][RIGHT]3104[/RIGHT]4/18/20154/21/20154/25/2015
[CENTER]3[/CENTER][RIGHT]3599[/RIGHT]4/23/2015
[CENTER]4[/CENTER][RIGHT]4004[/RIGHT]4/15/2015
[CENTER]5[/CENTER][RIGHT]7158[/RIGHT]4/25/2015
[CENTER]6[/CENTER][RIGHT]7158[/RIGHT]4/23/2015
[CENTER]7[/CENTER][RIGHT]7158[/RIGHT]4/23/2015
[CENTER]8[/CENTER]
[/size]
JL DataAdvFilt
…………… . And wot I finally want is this. Using Excel 2007
[size=4]-[CENTER]A[/CENTER][CENTER]B[/CENTER][CENTER]C[/CENTER][CENTER]D[/CENTER][CENTER]E[/CENTER][CENTER]F[/CENTER]
[CENTER]1[/CENTER]CodeDatestartdatestopdateCode
[CENTER]2[/CENTER][RIGHT]3104[/RIGHT]4/18/20154/21/20154/25/20153599
[CENTER]3[/CENTER][RIGHT]3599[/RIGHT]4/23/20157158
[CENTER]4[/CENTER][RIGHT]4004[/RIGHT]4/15/2015
[CENTER]5[/CENTER][RIGHT]7158[/RIGHT]4/25/2015
[CENTER]6[/CENTER][RIGHT]7158[/RIGHT]4/23/2015
[CENTER]7[/CENTER][RIGHT]7158[/RIGHT]4/23/2015
[/size]
JL DataAdvFilt
. . 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-questions/856824-visual-basic-applications-copy-all-unique-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
Sub AdvancedFilterMethod()
10 Dim wks1 As Worksheet: Set wks1 = ThisWorkbook.Worksheets("JL data") '
20
30 Dim lastrow As Long: Let lastrow = wks1.Cells(Rows.Count, "A").End(xlUp).Row
40 '
50 Dim rngCode As Range: Set rng = wks1.Range("A1:A" & lastrow & "")
60 'AdvancedFilter Run 1 to get Unique Codes for critical range in Run 2
70 rngCode.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wks1.Range("C10"), Unique:=True
80 'AdvancedFilter Run 2
90 rngCode.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("C10:________, CopyToRange:=wks1.Range("F2")
 
 
End Sub 'AdvancedFilterMethod()
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


This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of May 28, 2015.


For more resources for Microsoft Excel