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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Forum statistics

Threads
1,136,926
Messages
5,678,604
Members
419,774
Latest member
MooseWinooski

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
Top