advanced filter and union- please help

[mrh15]

Hi there

I have >66,000 rows of data that I want to put into an advanced filter.

Therefore I have put the data alongside in one sheet and have 2 ranges.

I have used the union method to set the data together - is there any way that this can be fed into an autofilter? Or can anyone think of an alternative work around?

Sub drop_down()
'Sheets("report").Select
Application.ScreenUpdating = False

Sheets("data").Select
Dim allrng As Range
Set allrng = Union(Range("raw_data"), Range("raw_data2"))



  allrng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("criteria_1"), CopyToRange:=Range("extract_1"), Unique:=False

End sub

 

[mrh15]

I know this is rude but *bump*

 

[Andrew Poulsom]

The List Range for Advanced Filter must be contiguous, in both Excel and VBA. So you are going to need three filters - raw_data, then raw_data2 below it, then the combination of those extracts.

Add a second extract range in some spare columns alongside extract_1 and name it extract_2. Then try:

Sub drop_down()
    Dim Rng As Range
    Application.ScreenUpdating = False
    Sheets("data").Select
    Range("raw_data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("criteria_1"), CopyToRange:=Range("extract_1"), Unique:=False
    With Range("extract_1")
        Set Rng = .End(xlDown).Offset(1, 0).Resize(1, .Columns.Count)
    End With
    Range("extract_1").Rows(1).Copy Rng
    Rng.Name = "extract_1Cont"
    Range("raw_data2").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("criteria_1"), CopyToRange:=Range("extract_1Cont"), Unique:=False
    Range("extract_1Cont").Rows(1).Delete Shift:=xlUp
    With Range("extract_1")
        Set Rng = Range(.Cells(1, 1), .End(xlDown)).Resize(, .Columns.Count)
    End With
    Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("criteria_1"), CopyToRange:=Range("extract_2"), Unique:=False
    Application.ScreenUpdating = True
End Sub

 

[mrh15]

Good man!

This place is so good!