Selecting Rows based on data

[JTL9161]

I have a spreadsheet where the data in column "D" today's date -1 (yesterday) and column 'J" has dates 1/1/19, 2/1/19, 3/1/19..etc.

I need to add to the bottom of my current macro something that finds the first cell with yesterday's date in column "D" and with 1/1/19 in column "J" and select the ROWS down to last cell with 1/1/19 or the cell above the cell that has 2/1/19 in it. Below is what I am looking for. The (entire) rows in red selected.

XXXXX	2/4/2019	2/5/2019	Future	1	1	VA	3/1/2019
	2/4/2019	2/5/2019	Future	1	1	PA	3/1/2019
XXXXX	2/4/2019	2/5/2019	Future	1	1	FL	3/1/2019
	2/5/2019		Retro			PA	1/1/2019
XXXXX	2/5/2019		Retro			IL	1/1/2019
XXXXX	2/5/2019		Retro			FL	1/1/2019
XXXXX	2/5/2019		Retro			IL	1/1/2019
XXXXX	2/5/2019		Retro			IL	1/1/2019
XXXXX	2/5/2019		Retro			IL	1/1/2019
XXXXX	2/5/2019		Retro			IL	1/1/2019
XXXXXXXXXX	2/5/2019		Retro			MO	2/1/2019
XXXXXXXXX	2/5/2019		Retro			CT	2/1/2019

<colgroup><col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody>
</tbody>

Hope my explaination is to confusing.
Thank you,
James

 

[jim may]

"I need to add to the bottom of my current macro something..." -- You might ought to post YOUR EXISTING MACRO too..

 

[KennyGreens]

Hi there, you can try something like this:

Dim MyDate As Date
Dim frow As Long
Dim lrow As Long
Dim rngFound As Range
Dim sFirst As String

MyDate = Date - 1

Set rngFound = Columns("D").Find(MyDate, Cells(Rows.Count, "D"), xlValues, xlWhole)
If Not rngFound Is Nothing Then
    sFirst = rngFound.Address
    Do
        If Cells(rngFound.Row, "J").Value = "1/1/2019" Then
            frow = rngFound.Row
        End If
        Set rngFound = Columns("D").Find(MyDate, rngFound, xlValues, xlWhole)
        lrow = rngFound.Row
    Loop While rngFound.Address <> sFirst
End If

Range("D" & frow, "D" & lrow).EntireRow.Select

Set rngFound = Nothing

 

[JTL9161]

Thanks for your input. I will give it a try.