Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union of Two Ranges with Advanced Filter (03)

    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

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    John:

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

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

    <pre>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:A 14"), Range("B1014"))

    oJoinNewRng01.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Workbooks(oWB).Sheets("Sheet1").Ran ge("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
    </pre>


  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    Here's what I came up with

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    As Steve remarked in his <post:=601,062>post 601,062</post:>, you can *not* use advanced filter on the union of two ranges:
    <hr>By "unioning" them the way you do they become 1 database with duplicate column headings and )only) the first ones are pulled<hr>
    (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.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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
    Attached Images Attached Images

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    It doesn't work on a discontiguous range.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union of Two Ranges with Advanced Filter (03)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •