PDA

View Full Version : [SOLVED] Advanced Filter



austenr
05-09-2005, 09:23 AM
Is it possible to extract the unique records from 2 lists in Sheet1 Columns A & B and write them to those columns in Sheet2 using Advanced Filter. Or VBA if there is a simple solution. Thanks

Bob Phillips
05-09-2005, 09:51 AM
The big problem with Advanced filter is that it needs to filter to the same sheet.

So i have filtered onto the same sheet, then moved it



Sub FilterData()
Dim rng As Range
Dim sh As Worksheet
Worksheets("Sheet1").Activate
Set rng = Range("F1:G12")
Set sh = ActiveSheet
With rng
.Offset(0, .Columns.Count).Resize(, .Columns.Count).EntireColumn.Insert
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Offset(0, .Columns.Count), _
Unique:=True
.Offset(0, .Columns.Count).Resize(, .Columns.Count).Cut _
Destination:=Worksheets("Sheet2").Range("A1")
.Offset(0, .Columns.Count).Resize(, .Columns.Count).Delete Shift:=xlToLeft
End With
End Sub

Zack Barresse
05-09-2005, 12:06 PM
The big problem with Advanced filter is that it needs to filter to the same sheet...

No it doesn't.

austenr
05-09-2005, 12:20 PM
I am confused...Which is right? Not that I am not greatful to both of you but what is correct and why?

Zack Barresse
05-09-2005, 12:31 PM
Well, it's not really about right and wrong, it's about efficient and inefficient. xld's way of doing it should work - which doesn't make it wrong. But you can cut out the whole copy part which makes it slightly less efficient. Here are a couple of examples ...


Option Explicit

Sub AFoneCol()
Dim rngFilter As Range, lngRow As Long
lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Set rngFilter = Sheets("Sheet1").Range("A1:A" & lngRow)
rngFilter.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), _
Unique:=True
End Sub

Sub AFtwoCols()
Dim rngFilter As Range, lngRow As Long
lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Set rngFilter = Sheets("Sheet1").Range("A1:B" & lngRow)
Range("A1:B31").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), _
Unique:=True
End Sub

Bob Phillips
05-09-2005, 12:33 PM
Zack is right, it is the activesheet, not the same sheet, so my move could be avoided by working from the target sheet not the source sheet.

Zack Barresse
05-09-2005, 01:33 PM
.. working from the target sheet not the source sheet.
And that's the key right there. Kind of a PITA by MS imho.

austenr
05-09-2005, 01:40 PM
I see where you are going. These examples can get me going. Thanks

austenr
05-09-2005, 01:53 PM
A question after running the second macro Zack, on the second sheet, i get the number 13. Not quite sure where that is coming from? Sure it is a simple explaination.

Zack Barresse
05-09-2005, 01:58 PM
Not sure what you mean. Is that part of your data?

austenr
05-09-2005, 02:07 PM
No. The number 13 does not appear in the data. Let me attach a sample.

austenr
05-09-2005, 02:08 PM
OOPS...

Zack Barresse
05-09-2005, 02:18 PM
Can't tell ya. Works for me. Although you altered the range to something a little wierd. Go over the ranges in these examples ...


Option Explicit

Sub AFoneCol()
Call clearMe
Dim rngFilter As Range, lngRow As Long
lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Set rngFilter = Sheets("Sheet1").Range("A1:A" & lngRow)
rngFilter.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), _
Unique:=True
End Sub

Sub AFtwoCols()
Call clearMe
Dim rngFilter As Range, lngRow As Long
lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Set rngFilter = Sheets("Sheet1").Range("A1:B" & lngRow)
rngFilter.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), _
Unique:=True
End Sub

Sub clearMe()
Sheets("Sheet2").Cells.Clear
End Sub

Routine clearMe is only for testing purposes.

And btw, you should add headers/labels to your data; not sure if that is only a test file or what.

austenr
05-09-2005, 02:48 PM
ok thanks

TonyJollans
05-09-2005, 04:39 PM
The big problem with Advanced filter is that it needs to filter to the same sheet.

No it doesn't.
Zack is right. Advanced Filter can go from any worksheet to any worksheet.



Zack is right, it is the activesheet, not the same sheet, so my move could be avoided by working from the target sheet not the source sheet.

This constraint is imposed when using the dialog but not when using code. As all the examples posted here are in code, there are no constraints.

If you want to do it without code go to Sheet2 and invoke the dialog (Data > Filter > Advanced Filter) - select your source range on Sheet1, select your target range on Sheet2 (A1?), check the appropriate boxes/buttons and press OK.