VBA - First and last row between dates

[eastrand]

I have a data set similar to the one below. In VBA, how do I find the first row and the last row in between two dates. In this example, between 01/10/2022 - 01/14/2022

Book2
	A	B	C	D
1	Date	Product	Qty	Sales
2	1/3/2022	Prod3	16	$ 70.72
3	1/3/2022	Prod1	24	$ 106.08
4	1/3/2022	Prod2	19	$ 83.98
5	1/3/2022	Prod2	14	$ 61.88
6	1/4/2022	Prod2	18	$ 79.56
7	1/4/2022	Prod3	23	$ 101.66
8	1/4/2022	Prod1	16	$ 70.72
9	1/10/2022	Prod2	18	$ 79.56
10	1/10/2022	Prod3	23	$ 101.66
11	1/10/2022	Prod2	23	$ 101.66
12	1/10/2022	Prod3	10	$ 44.20
13	1/11/2022	Prod3	21	$ 92.82
14	1/11/2022	Prod1	7	$ 30.94
15	1/11/2022	Prod1	23	$ 101.66
16	1/11/2022	Prod3	7	$ 30.94
17	1/12/2022	Prod1	20	$ 88.40
18	1/12/2022	Prod1	14	$ 62.22
19	1/13/2022	Prod2	14	$ 59.99
20	1/13/2022	Prod3	13	$ 57.75
21	1/14/2022	Prod1	13	$ 55.52
22	1/14/2022	Prod2	12	$ 53.28
23	1/15/2022	Prod3	12	$ 51.05
24	1/15/2022	Prod2	11	$ 48.81
25	1/16/2022	Prod3	11	$ 46.58
26	1/16/2022	Prod3	10	$ 44.35
27	1/17/2022	Prod1	10	$ 42.11
Sheet1

 

[DanteAmor]

Try this:

Sub finddates()
  Dim f As Range
  Dim first As Long, last As Long
  Dim d1 As Date, d2 As Date
  
  d1 = CDate("10/01/2022")
  d2 = CDate("14/01/2022")
  Set f = Range("A:A").Find(d1, , xlFormulas, xlWhole, , xlNext)
  If Not f Is Nothing Then
    first = f.Row
    Set f = Range("A:A").Find(d2, , xlFormulas, xlWhole, , xlPrevious)
    If Not f Is Nothing Then
      last = f.Row
      MsgBox "First row : " & first & ". Last row : " & last
    End If
  End If
End Sub

 

[eastrand]

Thanks @DanteAmor. This works if it finds both dates but sometimes there is not always data on the start or finish date. Is there a way the find the row of the first date on or after the start date and on or before the finished date? Sorry for not explaining in advance.

 

[eastrand]

I think I got it. Below if anyone else needs it. Feel free to approve upon.

Sub test3()
Dim rng As Range
Dim SD As Variant
Dim ED As Variant
Dim rowstartnum As Long
Dim rowendnum As Long

 
Set rng = Range("P:P")
SD = 44571 'Start Date
For Each cll In rng
If cll.Value >= SD And IsDate(cll.Value) Then
    rowstartnum = cll.Row
    Exit For
End If
Next

ED = 44575 'End Date
For Each cll In rng
If cll.Value >= SD And cll.Value <= ED And IsDate(cll.Value) Then
   If cll.Row > rowendnum Then rowendnum = cll.Row
End If
Next

MsgBox rowstartnum & "  " & rowendnum
End Sub

 

[DanteAmor]

Whitout loop

Sub finddates()
  Dim f As Range
  Dim first As Long, last As Long
  
  Set f = Range("A:A").Find(CDate("10/01/2022"), , xlFormulas, xlWhole, , xlNext)
  If Not f Is Nothing Then first = f.Row
  Set f = Range("A:A").Find(CDate("14/01/2022"), , xlFormulas, xlWhole, , xlPrevious)
  If Not f Is Nothing Then last = f.Row
  MsgBox "First row : " & first & ". Last row : " & last
End Sub

 

[eastrand]

Again, thanks. That's much simpler.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.