• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofilter a column with values from another column

S

Sudarshan

New Member
Hi All,

I have a requirement to use autofilter macro. The column to filter is in sheet 1 and would be A1 and the contents to filter are present in sheet2 in the range A1:A20

I am using the below code to auto filter based on a cell value.

It is the criteria in the below which i need to look for all values between cell A1 and A20

Range("A1").Select
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Range("G1").Value

If someone has any codes please help.

Sudi
 
Hi Sudi,

Instead of AutoFilter.. try to use Advanced Filter.. like this..

Code: 
    Sheets("Sheet1").Range("A1:A" & Range("A" & Columns.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, _[/FONT]
[FONT=courier new]    CriteriaRange:=Sheets("Sheet2").Range("A1:A20")[/FONT]
[FONT=courier new]
 
Hi Debraj,​
​
Thanks a lot for the help it really helped me.. I may be asking for more.. but is there some code that we can add to copy the same into a new sheet​
 
Debraj,

sorry but teh code doesnt work.. i used a advanced filter manually it worked.

The data is in sheet named EA and range in sheet named "1".

Sub Autofilter_Range()


Sheets("EA").Range("A1:AB1" & Range("A" & Columns.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("1").Range("A2:A5")

End Sub
 
Hi Sudarshan,

Try using the below, this should suffice your need..
Code: 
Sub Auto_filter()
Sheets("Sheet1").Select 'change sheet as required
Range("A1").Select
ActiveSheet.AutoFilterMode = False
icell = Sheets("Sheet2").Range("G1").Value 'change as required
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=icell
Rows("2:25").Select 'if you want to include header then change it to Rows("1:25")
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet3").Select 'change as required
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub

Hope this helps...:)
 
Hi Sudi,

Few things about advanced filter..

Top cell of criteria range.. should need to be same as in Range field's required columns header..
So if in EA.A1.. header is "HEADER", then 1.A1, should also need to be "HEADER"..

Advance filter, will automatically find the column named "HEADER" and apply filter on it..
 
Hi Deb,

Yes thats right , i have ensured there are header at both places. still it runs without an error no advance filter happens though.

Sudi
 
I think we have a bit confusion here...
Sheet1(A1:A20) is your criteria? So where is the range wher you want the filter?
by range of criteria, you mean you have various criterias for various columns(eg.A1 is criteria for column A, A2 for Col.B,etc..) or you need to loop thru the criteria only in a single column?:confused:
 
can you please upload sample file..

___

but as per your provided code..
need to change like this..

Sheets("EA").Range("A1:AB" & Range("A" & Columns.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("1").Range("A1:A4")
 
@Debraj (ex-Roy)
Hi, buddy!
Why not changing your signature subtracting 260 in octal? :p
Regards!
 
Hi Abhi,

The range where i need the filter on in col a and the values which needs to be filtered is in Col A of another sheet.

Sudi
 
Hi Deb,

You code works perfect.. many thanks for making this happen for me.

Two questions though, can i use the copy paste code provided by Abhi in the same code.

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet3").Select 'change as required
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

Second Can i have a code in the beginging which would erase any data present in sheet3 prior to pasting values.

Thanks
Sudi
 
Yes you can..


Code: 
Sub test()
With Sheets("EA").Range("A1:AB" & Range("A" & Columns.Count).End(xlUp).Row)
    .AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("1").Range("A1:A4")
    Sheets("3").Cells.Clear
    .SpecialCells(12).Copy Sheets("3").Range("A1")
End With
End Sub
 
You must log in or register to reply here.
Back
Top