VBA code to return all rows matching multiple criteria

erenkey

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top