Great Difficulty Getting Results From VBA Advanced Filter

[Ark68]

I am simply trying to filter data according to my advanced criteria range in worksheet "ws_fac" (in 2nd workbook) and copy it over to a range starting at AN3 in worksheet "ws_lists" (of 1st primary code holding workbook)

Here is the code I'm struggling with:

If ws_fac.FilterMode Then ws_fac.ShowAllData                                              'turn off any filtering if data is filtered
ws_lists.Range("AN3").CurrentRegion.Offset(1).ClearContents                      'clear the destination range (filtered data to be copied here)
Set rgData = ws_fac.Range("A1").CurrentRegion                                           'define the source range (all data in ws_fac including header row 1)
Set rgCriteriaRange = ws_lists.Range("AI2:AJ3").CurrentRegion                   'criteria range (AI3 AND AJ3)
Set rgCopyToRange = ws_lists.Range("AN3").CurrentRegion.Rows(1)          'destination target (ws_lists starting at AN3)
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopyToRange        'apply filter
lrfaclst = ws_lists.Cells(ws_lists.Rows.Count, "AN").End(xlUp).Row                'get the value of the last row of copied data range
ws_lists.Range("AN" & lrfaclst + 1) = "Nothing suitable."                             'add a value to the end of the column of data

The line in red is giving me a "The extract range has a missing or invalid field name." I'm unsure why. The column headers match the reference headers in my data. All the columns in my source data have a header label, and no column is without a header label.

Here is the structure of my source data worksheet:

Facilities.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	hh FIELD (do not alter)	AN RESOURCE TYPE	CLASS	RESP	TYPE	UNIT	LIT	LABEL	Washroom	WR_Code	FAMILY	WP_Side	GROOM	ACT-PASS	CONFIG	GOALS	PARK	Address
2	hhhhhhhhhhhhhh	hhhhhhhhhh	B	BP	hhhh	1	N	hhhhhhhhhhhhhhhhh	N	x	BP		CRP	A			hhhh
3	llllllll	lllllllll	A	BP	llll	1	Y	lllllllllllllll	Y	x	BP		CRP	A			lllll
4	mmmmmmmmmmmmm	mmm	B	BP	Field	1	N	mmmmmmmmm	N	x	BP		na	A	NC	NA	mmm
5	mmmmmmmmmmmmm	mmm	A	BP	Field	2	Y	mmmmmmmmm	N	x	BP		na	A	S1 90m x 61.5m	NA	mmm
Facilities

Here is my criteria range:

SignatureSheets.xlsm
	AI	AJ
1	Advanced Filter1
2	ACT-PASS	LABEL
3	P	WP*
lists

And finally, the structure of my destination:

SignatureSheets.xlsm
	AN	AO
1	FacList
2	ACT-PASS	LABEL
3
4
5
6
lists

I don't yet quite understand VBA based advanced filtering so I'm struggling to figure out where I need to make corrections. Be very grateful for any one that can offer up some advice, solutions.

 

[Alex Blakenburg]

I don't believe the Advanced Filtering part is what is causing you the issue but rather your use of Current Region for the Criteria and Destination.
Can you clarify that you are only expecting to output 2 columns ?

 

[Ark68]

Hi Alex, thank you so much for considering my issue. You are likely very correct as I have no idea what the CurrentRegion arguments are meant to do. I have been self teaching with tutorials and in the instance I was following they were used.

Yes, if it is possible, to only copy the ACT-PASS and LABEL columns of the filtered data (from AND results of the ACT-PASS and LABEL criteria).

 

[Alex Blakenburg]

Do you understand what CurrentRegion does and it is just the arguments you are having issues with ?
For what current region does see either of the below:
(selects the area around a nominated cell or range until it hits a blank row and blank column)
VBA Current Region - Automate Excel
How To Use VBA Select The Current Region In Excel

Then tell me if you are stuck on the arguments.

 

[Alex Blakenburg]

In terms of the code, see if this helps.

Sub TestAdvancedFilter_modv02()

    Dim ws_fac As Worksheet, ws_lists As Worksheet
    Dim rgData As Range, rgCriteriaRange As Range, rgCopyToRange As Range
    Dim lrfaclst As Long
    
    Set ws_fac = Worksheets("Facilities")
    Set ws_lists = Worksheets("lists")    

    If ws_fac.FilterMode Then ws_fac.ShowAllData                                    'turn off any filtering if data is filtered
    ws_lists.Range("AN3").CurrentRegion.Offset(2).ClearContents                     'clear the destination range (filtered data to be copied here)
    
    Set rgData = ws_fac.Range("A1").CurrentRegion                                   'define the source range (all data in ws_fac including header row 1)
    Set rgCriteriaRange = ws_lists.Range("AI2:AJ3")                                 'criteria range (AI3 AND AJ3)
    Set rgCopyToRange = ws_lists.Range("AN2").CurrentRegion.Rows(2)                 'destination target (ws_lists starting at AN3)
    
    rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopyToRange              'apply filter
    lrfaclst = ws_lists.Cells(ws_lists.Rows.Count, "AN").End(xlUp).Row              'get the value of the last row of copied data range
    If lrfaclst = 2 Then ws_lists.Range("AN" & lrfaclst + 1) = "Nothing suitable."  'add a value to the end of the column of data

End Sub

 

[Alex Blakenburg]

See if this helps:

 

[mikerickson]

Have you checked all the headers for trailing spaces?

 

[Ark68]

Hi Alex,
I really appreciate the effort put into helping me with this. CurrentRegion seems pretty straight forward. I've been defining ranges in a very inefficient way knowing now how CurrentRegion can help do the same thing. But I also feel it could cause problems if not fully understood.
With the illustration you provided in posts 6, I know understand better where your code corrections make sense,so I thank you for providing both. Things now work as needed. Thank you.

I am curious about the Offset(2) reference in the ClearContents line. I've been playing around with offset, but still not comfortable enough to use it for it's full power. Not sure yet if I undersstand how the offset affected the CurrentRegion. I think it was needed to avoid clearing the contents of AN1:AO2. If I understand CurrentRegion correctly, that if I copied my data to AN4 instead of AN3 (therfore leaving cells AN3:AO3 blank) that this code would do the same:

ws_lists.Range("AN4").CurrentRegion.ClearContents

Which brings me to the rgCopyToRange statement where rows(2) was added. I assume isolates the needed data by pointing it to the second row of the "CurrentRegion" (which consists of 2 rows and 2 columns). Would not :

Set rgCopyToRange = ws_lists.Range("AN2:AN3")

not accomplish the same?

 

[Ark68]

Have you checked all the headers for trailing spaces?

Hi Mike ... yes, that was the first thing I checked as it made sense to believe something wasn't right with the headers. The spelling was all correct, so I used LEN to determine if the header values had spaces I couldn't see. They all revealed that the values were just as they appeared.

 

[Alex Blakenburg]

Using the image below:
Ideally you don't want to have an additional heading row "up against the table" you are working with.
So you want to leave a blank row between Advanced Filter1 in AI and the data under it
and also for FacList in AN and the data under it.
If you do that then CurrentRegion based on ACT-PASS in either AI and AN would not pick up the additional and irrelevant row.

Excel's Filter, Sort, Create Pivot & Convert to table functions also use current region, so having the blank row separating what should be the column headings from data above it helps with those function as well.

The VBA offset function has 2 parameters
.Offset(ByNoOfRows, ByNoOfColumns).
If you only want to Offset by rows you can leave out the latter and just put
.Offset(ByNoOfRows)

Offset by rows moves the whole range area you are working with down by that no of rows.
So Range("A10:E13").Offset(5) becomes Range("A15:E18").
CurrentRegion is just a range and works the same way.
Rows and Columns and Cells are determined at the sheet level if you don't qualify it using a range but if you use a range then it is range dependant.

• Rows(2).Address gives $2:$2 (Row 2 of the Sheet)
  but
• Range("A10:E13").Rows(2).Address gives $A$11:$E$11 (Row 2 of the Range("A10:E13"))