Union of Two Ranges with Advanced Filter (03)

Topic

New Reply

I am having difficulty using the “union” within the advanced filter code. I know that the union of the two ranges is set correctly because I can select the union of the two ranges.

I have attached a sample file.

Thanks,
John

Reply | Quote

Replies

John:

What was the result of the John routine supposed to be?

I was able to eliminate the error by the following modification:

Sub john()
On Error GoTo Err_john

   Dim oWB As String
   Dim oJoinNewRng01 As Range
   
   oWB = Application.ActiveWorkbook.Name
   
   Set oJoinNewRng01 = Union(Workbooks(oWB).Sheets("Sheet1").Range("A10:A14"), Range("B10:D14"))
   
   oJoinNewRng01.AdvancedFilter Action:=xlFilterCopy, _
         CriteriaRange:=Workbooks(oWB).Sheets("Sheet1").Range("Criteria"), _
         CopyToRange:=Workbooks(oWB).Sheets("Sheet1").Range("I10:L10"), _ 
         Unique:=False

Exit_john:
   Exit Sub
   
Err_john:
   MsgBox Err.Number & " - " & Err.Description
   Resume Exit_john
   
End Sub

Reply | Quote

The end result is it do an extract on the 1st union and then another on the 2nd union. I was stuck on the code and did not include the second pass.

Your suggestion worked.

Thanks,
John

Reply | Quote

Mike,

Do the cells have to be contiguous? I tried setting a new union based on the 2nd range union and the code errors out.

Thanks,
John

Reply | Quote

John:

I’ve never tried to do an Advanced Filter on non-contiguous ranges. I took the “cowards” approach and put your blue region below the green.

I haven’t used the union function before.

Reply | Quote

Here’s what I came up with

Reply | Quote

Mike,

I’m in the process of surfing the web and have yet to come up with a solution. Hopefully someone will provide some insight.

Regards,
John

Reply | Quote

I think the solutions are:
1) to setup the data into 1 database and do it directly
2) to do 2 extracts (but you would have to add accounts to the second and then combine them into one list

By “unioning” them the way you do they become 1 database with duplicate column headings and the first ones are pulled

Steve

Reply | Quote

Steve,

I’m focused back on the non-contiguous cells and trying to perform an advanced filter on the union of the ranges.

From your post I take it that an advanced filter can be preformed on the union.

I have attached a modified example.

Thanks,
John

Reply | Quote

As Steve remarked in his post 601,062, you can *not* use advanced filter on the union of two ranges:[indent]

---

By “unioning” them the way you do they become 1 database with duplicate column headings and )only) the first ones are pulled

---

[/indent](Italics mine)

I’d go with the suggestion by Mike, which is also suggestion 1 by Steve: Combine the two ranges into one, with the data below each other. You then have one contiguous range to work with.

Reply | Quote

Hans,

As I understand Steve’s comments, the advanced filter can not function with the union of two ranges because of the dupliclate column headings.

Under the senario where a union is made between an account field and data field (no duplicate column headings) would the advanced filter still work?

Within the attached file of my last post, I changed the code to reflect a union between Range(“A10:A14”) and Range(“E10:G14”). Please note that E10 to G10 does not have duplicate column headings.

Thanks,
John

Reply | Quote

It doesn’t work on a discontiguous range.

Reply | Quote

Hans,

I would have thought the Union of the two discontiguous ranges would have made it one contiguous range. Apparently this is not the case.

Regards,
John

Reply | Quote

No, it’s still the union of discontiguous ranges, just like selecting a range, then selecting another range with Ctrl held down doesn’t make the selection a single contiguous range.

Reply | Quote