advanced filter and union- please help

mrh15

New Member
Joined
Oct 20, 2003
Messages
30
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?

Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,216,496
Messages
6,130,984
Members
449,612
Latest member
geniusufo007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top