Excel Advanced Filter

[Dimitriy]

Hello Everyone,

Question: Is it possible in Excel to assign a list range for my Advanced Filter that uses lists from more than one worksheet? I've tried several ways, but nothing works.

Thanks,
Dimitriy

[mdmackillop]

Hi Dimitry,
Welcome to vbax.
Can you make up and post a small sample to demonstrate what you are after. Use Manage Attachments in the Go Advanced reply section to post it.
Regards
MD

[Dimitriy]

Hey MD,

Thanks for your quick reply. Take a look at the attached sample file. Here is what I am trying to do in the Advanced Filter window:

List Range:'list 1'!$A$3:$B$20+'list 2'!$A$3:$B$20
Criteria Range: 'main page'!$E$3:$F$4
Copy to: 'main page'!$A$3:$B$30

I am trying to get unique records only, and it comes back with an error message: reference is not valid. So i am wondering if there is a way to combine the lists the way i am doing it in the "List Range"?

Thanks,
Dimitriy

[mdmackillop]

This writes the data to Main Page and filters it from there
[VBA]
Option Explicit
Sub Unique()
Dim Rng As Range
Application.ScreenUpdating = False
With Sheets("List 1")
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(1, 13)
End With
With Sheets("List 2")
Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(Rows.Count, 13).End(xlUp).Offset(1)
End With
With Sheets("Main Page")
Set Rng = Range(.Cells(1, 13), .Cells(Rows.Count, 13).End(xlUp)).Resize(, 2)
End With
With Rng
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"A2:B2"), Unique:=True
.ClearContents
.Borders.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
Application.ScreenUpdating = True
End Sub
[/VBA]

[Dimitriy]

MD,

Thank you so much for your help. The code you provided works great. I am not familiar with the "With" function, so could you possibly explain what exactly is happening in the code pasted below:

With Sheets("List 1")
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(1, 13)
End With
With Sheets("List 2")
Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(Rows.Count, 13).End(xlUp).Offset(1)
End With
With Sheets("Main Page")
Set Rng = Range(.Cells(1, 13), .Cells(Rows.Count, 13).End(xlUp)).Resize(, 2)
End With

Specifically, all the cell references (ex. Cells(2, 1)? ). Also it looks like for the Advanced filter you are not using the criteria E3:F4 range, are you?

Thanks in advance.

-Dimitriy

[mdmackillop]

With works with "." as a shortcut to avoid repeating the With value. Otherwise the first statement would be

[VBA]Range(Sheets("List 1").Cells(2, 1), Sheets("List 1").Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(1, 13)
[/VBA]

[VBA]
'Copy list 1 to Main page
With Sheets("List 1")
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(1, 13)
End With
'Copy List 2 to main page below list 1
With Sheets("List 2")
Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy Sheets("Main Page").Cells(Rows.Count, 13).End(xlUp).Offset(1)
End With
'Get combined range
With Sheets("Main Page")
Set Rng = Range(.Cells(1, 13), .Cells(Rows.Count, 13).End(xlUp)).Resize(, 2)
End With
[/VBA]

To extract unique items from this list does not require criteria.

[Bob Phillips]

Not only that, it is more efficient, as it sets the pointer to the data just once, it does not have to reset it each time it want that data.

And it is more readable as it removes duplication.

Everyone should use it.

[Dimitriy]

Hey MD,

My list tables also have additional data below the ingredient tables, .End(xlUp) code doesn't work because it's also picking up that data. Is there a way to work around this? Please see the updated sample file.

I was also wondering if you could explain why you are using the ".Resize(, 2)" code?

Thank you,
Dimitriy

[mdmackillop]

My list tables also have additional data below the ingredient tables, .End(xlUp) code doesn't work because it's also picking up that data. Is there a way to work around this? Please see the updated sample file.

In that case you need xldown, which relies on data being "solid"
[VBA]
Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)).Resize(, 2).Copy Sheets("Main Page").Cells(1, 13)
[/VBA]

I was also wondering if you could explain why you are using the ".Resize(, 2)" code?

The first part identifies a single column range, Resize extends by the stated number of rows/columns.

Please check VBA Help for an explanation of keywords etc.

[Dimitriy]

MD,

Everything seems to be working now, Thank you very much for your help!