Results 1 to 2 of 2

Macro to filter multiple columns simultaneously

This is a discussion on Macro to filter multiple columns simultaneously within the Excel Questions forums, part of the Question Forums category; Hi, I m a newbie to this, Need help to filter multiple columns using input boxes with 4 different criterias ...

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    1

    Default Macro to filter multiple columns simultaneously

    Hi,

    I m a newbie to this, Need help to filter multiple columns using input boxes with 4 different criterias at one go using vba macro and paste onto a new sheet.

    Here's a sample Data

    col-A col-B col-C col-D col-E col-F
    SPOC RECNAME DOBIRTH QUALIFICATION DORESG UNIV
    Rajeev Manoj 12-06-1981 BSC 10-04-06 Mumbai
    Naveen Vinod 04-05-1980 BCom 19-08-09 Mumbai
    Shree Ramesh 17-05-1984 MBA 21-05-07 Mumbai
    ....
    We have this kind of data for approx 5000 rows,

    Now here's what I need
    From the above data on sheet1 I need to filter on the first four columns

    I need to auto fileter on the criteria's as mentioned in the input box. The input box will be for user to enter desired filtering option to fileter on the specific coulmn. He can select all or any of the options or only one option leaving other boxes blank. Depending on the input in the box the macro should then filter on all the required columns and pull the information to a nes sheet say Sheet2

    Say for instance I need
    SPOC : Rajeev
    RECNAME : Manoj
    DOBIRTH : 12-06-1981
    QUALIIFCATION : BSC

    I hope I have given all information. In case if anyone needs more data I can help them out. Please if anyone can help me with it will be great.

    Thnaks & Regards
    Joby

  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,796

    Default Re: Macro to filter multiple columns simultaneously

    In my regional preference the date is entered as mm/dd/yy so your database looks in my sheet as

    SPOC RECNAME DOBIRTH QUALIFICATION DORESG UNIV
    Rajeev Manoj 6/12/1981 BSC 10-4-2006 Mumbai
    Naveen Vinod 5/4/1990 BCom 19-08-09 Mumbai
    Shree Ramesh 5/17/1984 MBA 21-05-07 Mumbai

























    In the door sg put a single apostrophe(') in the beginning and type the doorsg so that it will not be automatically converted to date by excel. so that these are not numbers.

    I am giving two macro "test" and "undo"
    as the results are going to be parked in sheet2. the macro undo clears this sheet(sheet2)

    when the input boxes come up if you do not want to filter according to that criteria then leave it blank and click ok EXCEPT WHEN THE DOB INPUT BOX COMES UP YOU DO NOT LEAVE IT BLANK BUT TYPE 0 (ZERO). BE CAREFUL ON THIS. if you want to consider then you can enter the date as ENTERED in the sheet e.g. 6/12/81
    IN THE INPUTBOXES THE SPELLING OF INPUT SHOULD BE EXACT.

    The result of the autofilter will be in sheet2

    the macros are

    Code:
    Sub test()
    Dim spoc As String, nm As String, dob As Date, qual As String
    Dim r As Range
    Dim doorsg As String, univ As String
    
    Worksheets("sheet1").Activate
    ActiveSheet.AutoFilterMode = False
    
    spoc = InputBox("type spoc, if not required leave it blank and click ok")
    nm = InputBox("type recname, if not required leave it blank and click ok")
    MsgBox "IF YOU DO NOT WANT TO CONSIDER DOB BE CAREFUL TOENTER 0"
    dob = CDate(InputBox("type DOB, if not required ENTER 0 and click ok"))
    qual = InputBox("type qualification, if not required leave it blank and click ok")
    doorsg = InputBox("type doorsg, if not required leave it blank and click ok")
    univ = InputBox("type univ, if not required leave it blank and click ok")
    Set r = ActiveSheet.UsedRange
    If spoc <> "" Then
    r.AutoFilter Field:=1, Criteria1:=spoc
    End If
    If nm <> "" Then
    r.AutoFilter Field:=2, Criteria1:=nm
    End If
    If CDate(dob) <> 0 Then
    r.AutoFilter Field:=3, Criteria1:=dob
    End If
    If qual <> "" Then
    r.AutoFilter Field:=4, Criteria1:=qual
    End If
    If doorsg <> "" Then
    r.AutoFilter Field:=5, Criteria1:=doorsg
    End If
    If univ <> "" Then
    r.AutoFilter Field:=8, Criteria1:=univ
    End If
    r.Cells.SpecialCells(xlCellTypeVisible).Copy
    With Worksheets("sheet2")
    .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
    End With
    ActiveSheet.AutoFilterMode = False
    Application.CutCopyMode = False
    End Sub
    Code:
    Sub undo()
    Worksheets("sheet2").Cells.Clear
    End Sub
    Last edited by venkat1926; May 23rd, 2010 at 01:27 AM.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com