VBA code to return all rows matching multiple criteria

erenkey

Board Regular
Joined
Dec 9, 2005
Messages
158
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,179
Office Version
2007
Platform
Windows
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("[COLOR=#ff0000]Sheet1[/COLOR]")
   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, ">=[COLOR=#ff0000]01/01/2019[/COLOR]", xlAnd, "<=[COLOR=#ff0000]01/05/2019[/COLOR]"
         sh.Range("A1").AutoFilter 3, Ky
         sh.PrintOut
      Next Ky
   End With
   sh.ShowAllData
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,336
Messages
5,486,246
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top