• 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.

Extract data using Advanced Filter and VBA

Vadi

New Member
Hi, The below code for Advanced filter doesnt work for me, it extracts only the header data from table. The link from where the code is taken is given below. Any help please

http://chandoo.org/wp/2012/11/27/extract-subset-of-data/

Code:
Sub FilterExtract()

Sheets("Filter").Select
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("RawData").Range("J1:L2"), CopyToRange:=Sheets("Filter").Range("A6"), Unique:=True

Columns.AutoFit
Sheets("Filter").Range("A6").Select


End Sub
 
Hi ,

Since the code works in the blog's workbook , it should work in yours too , unless your workbook has something different. If you upload your workbook , it will make it easy to find out what the problem is.

Otherwise , people can make suggestions , which you will have to confirm / deny , all of which is just going to waste time.

Narayan
 
It's always good to upload sample file when u are getting stucked in VBA cases as same also said forum rules too.

Pls check with herewith attached sample file.

Code:
Option Explicit

Sub FilterExtract()

Sheets("Filter").Range("A6").CurrentRegion.Clear

Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("RawData").Range("J1:L2"), CopyToRange:=Sheets("Filter").Range("A6"), Unique:=True

Sheets("Filter").Activate
Columns.AutoFit
Sheets("Filter").Range("A6").Select

End Sub
 

Attachments

  • Sample_Advance_Filter.xlsm
    18.5 KB · Views: 8
Hi ,

Since the code works in the blog's workbook , it should work in yours too , unless your workbook has something different. If you upload your workbook , it will make it easy to find out what the problem is.

Otherwise , people can make suggestions , which you will have to confirm / deny , all of which is just going to waste time.

Narayan

Hi Narayan,
The code is working fine. The problem is with the criteria. After changing the criteria it works fine. Thanks for your support
 
It's always good to upload sample file when u are getting stucked in VBA cases as same also said forum rules too.

Pls check with herewith attached sample file.

Code:
Option Explicit

Sub FilterExtract()

Sheets("Filter").Range("A6").CurrentRegion.Clear

Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("RawData").Range("J1:L2"), CopyToRange:=Sheets("Filter").Range("A6"), Unique:=True

Sheets("Filter").Activate
Columns.AutoFit
Sheets("Filter").Range("A6").Select

End Sub

Hi Deepak,

The code works fine. thanks for your support
 
Kudos !!! to create such a awesome Macro
coming to my query I have large number of banks name around 1500 so dropdown won’t looks good, is there any way to enter bank name manually and with partial name match also needs to work Example full bank name is database is “BANQUE MAROCAINE DU COMMERCE” by entering BANQUE should show the related rows in result. hence please help me with this regards.
Tons of thanks in advance !!!!
icon_smile.gif


please find the enclosed excel sheet for your reference.
 

Attachments

  • Macro work 09mar15.xlsm
    137 KB · Views: 3
Kudos !!! to create such a awesome Macro
coming to my query I have large number of banks name around 1500 so dropdown won’t looks good, is there any way to enter bank name manually and with partial name match also needs to work Example full bank name is database is “BANQUE MAROCAINE DU COMMERCE” by entering BANQUE should show the related rows in result. hence please help me with this regards.
Tons of thanks in advance !!!!
icon_smile.gif


please find the enclosed excel sheet for your reference.
Guys I really appreciate if anyone help me out with this issue :)
 
Guys I really appreciate if anyone help me out with this issue :)

In a simple approach check attached.

To make it dynamic/or further changes you are requested to start a new thread for the same with a ref a this post as here you are hijacking someone's post.
 

Attachments

  • Macro work 09mar15_1.xlsm
    133 KB · Views: 12
In a simple approach check attached.

To make it dynamic/or further changes you are requested to start a new thread for the same with a ref a this post as here you are hijacking someone's post.
Deepak you are a Champ !!! :)

I'll work on further and get back to you if I have any further thoughts.
 
Deepak you are a Champ !!! :)

I'll work on further and get back to you if I have any further thoughts.
Deepak I apologies I dint mentioned that this Macro need to work from Ms office 2003 to 2013 version (Currently this is not working with 2003 )as I need to spread this data base to different teams and they might use different versions, please help me.
 
Deepak I apologies I dint mentioned that this Macro need to work from Ms office 2003 to 2013 version (Currently this is not working with 2003 )as I need to spread this data base to different teams and they might use different versions, please help me.


As i said earlier Pls start a new post for the same by referring this one.
 
Code:
Sub FilterData()
    [b10].CurrentRegion.Cells.Clear
    Range("DataRng").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("cr"), CopyToRange:=Range("b10"), Unique:=True
    Columns("D:p").AutoFit
    Range("B10").Select
  
    If Not [b10].CurrentRegion.Rows.Count > 1 Then _
        MsgBox "No service/information available with this search criteria.", vbInformation, "Data Not Found"
  
End Sub
 
Back
Top