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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,573
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,245
Messages
5,510,051
Members
408,776
Latest member
bricegoing

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top