VBA code to return all rows matching multiple criteria
Results 1 to 2 of 2

Thread: VBA code to return all rows matching multiple criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to return all rows matching multiple criteria

    I have a spreadsheet that contains multiple rows of data for multiple dates and companies. I am looking for a way to show all of the entries for the specified company within a specified date range. For example, I want to see all of the entries for company ABC between 1/1/2019 and 1/5/2019. I ultimately ant to be able to have a different print out for each company that is listed. Is this doable through VBA?

    A B C D E
    Date Init Bus.Unit Contact Name Notes & Comments
    1/1/2019 EE ABC John Smith Went to dinner
    1/1/2019 EE ABC Billy Bob Went to ball game
    1/2/2019 NH CDE Chris Jackson Scheduled conference call
    1/3/2019 JB XYZ Tim Alexander Went to ball game
    1/4/2019 BW ABC John Smith Customer is happy
    1/4/2019 NH XYZ Tim Alexander Customer is happy
    1/5/2019 RH XYZ Tim Alexander Went to dinner
    1/6/2019 BW CDE Chris Jackson Sent customer pricing
    1/7/2019 JB ABC John Smith Customer want to discuss options

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA code to return all rows matching multiple criteria

    To print each company in the range of dates.

    Change data in red by your information.


    Code:
    Sub company()
       Dim Cl As Range, sh As Worksheet, Ky As Variant, lr As Long
       
       Set sh = Sheets("Sheet1")
       lr = sh.Range("A" & Rows.Count).End(xlUp).Row
       With CreateObject("scripting.dictionary")
          For Each Cl In sh.Range("C2", sh.Range("C" & Rows.Count).End(xlUp))
             If Cl.Value <> "" Then .Item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .Keys
             sh.Range("A1:E" & lr).AutoFilter 1, ">=01/01/2019", xlAnd, "<=01/05/2019"
             sh.Range("A1").AutoFilter 3, Ky
             sh.PrintOut
          Next Ky
       End With
       sh.ShowAllData
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

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
  •