Skip to main content

Search functionality using Advanced Filter Technique through VBA

Hello Every One,

This time I have come up with another VBA  trick which could be used as a replacement for traditional Index(table,Small(if(Column = Criteria, RowNum),nth)) constructions. This is much efficient than any way around based on Excel general Functions like I have stated earlier. So, for the sake explanation, there are two sheets. One is a Master Sheet which would contain Data in the form of a table having different Headings which could be used as criteria either individually or otherwise. Second sheets is an Extract sheet where data would be pulled from Master sheet based on Some criteria placed in the Extract sheet itself.

here are some pictures of Master sheet layout and Extract Sheet.


Master Sheet:-





Extract Sheet:-






















Here is the code which pulls the records from master sheet based on Town - New York Criteria.


       
Sub AdvFil()
    'Declaring Variables
    Dim mRng As Range       'Master Table Range
    Dim cRng As Range       'Criteria Range
    Dim dRng As Range       'Destination range
    
    Set mRng = Sheets("Master").Range("A1").CurrentRegion       'List Range
    Set cRng = Sheets("Extract").Range("F1").CurrentRegion      'Criteria Range
    Set dRng = Sheets("Extract").Range("A1")                    'Destination cell
    
    dRng.CurrentRegion.Clear                            'Clearing Previous Content
    
    'Using Advanced filter to Pull rows
    mRng.AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=cRng, _
                        CopytoRange:=dRng, _
                        Unique:=False
    
    dRng.CurrentRegion.Columns.AutoFit                  'Auto fitting columns
        
End Sub

The code is just replicating the manual advanced filter process performed through Excel interface. While using excel interface, It demands List Range ( represented by mRng ), Criteria Range ( represented by cRng in the Code and a Copy to Range ( represented by dRng ).

Note:- Its important that the Column heads (Town) in the Criteria Range (Extact!F1:F2) should exactly match with Column heads in the List Range Or Master Sheet.

you can then assign this macro to a Button and extract records by clicks.

the Criteria Range has plenty of applications which you could find in the links below.
http://www.contextures.com/xladvfilter01.html
http://www.contextures.com/xladvfilter02.html

This really is powerful tool which can handle huge rows of data easily.

Now its your time to give it a try and learn.

Regards,
Vikas Gautam
Labels:

Comments

Post a Comment

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more