Macro to select rows based on dates

[Code Numpty]

I have a spreadsheet with columns of data and dates. I want to be able to
select rows based on a start date and an end date.

example data

13/9/09 Sun 21/9/09 Mon I2 Longman
14/9/09 Mon 22/9/09 Tue 19 Andrews
15/9/09 Tue 21/9/09 Mon 12 Best
17/9/09 Thu 23/9/09 Wed 15 James
17/9/09 Thu 5/10/09 Mon 22 Wilson
17/9/09 Thu 22/9/09 Tue 7 Hole
18/9/09 Fri 18/9/09 Fri 6 Ratcliffe
18/9/09 Fri 3/10/09 Sat 11 Jones

Can I get a macro to select from the first row containing 13/8/09 in column
A to the last row containing 18/9/09 in column A?

 

[JLGWhiz]

Assuming that the example data is in columns A thru F and rows 2 thru last
row.

Sub selDate()
Dim lstRw As Long, ws As Worksheet
Dim r1 As Range, r2 As Range, rng As Range
Dim x As String, y As string

Set ws = ActiveSheet
lstRw = ws.Cells(Rows.Count, 1).End(xlUp).Row
firstDt = InputBox("Enter beginning date", "START DATE")
secndDt = InputBox("Enter ending date", "END DATE")
Set rng = ws.Range("A2:A" & lstRw)
Set r1 = rng.Find(CDate(firstDt), LookIn:=xlValues)
Set r2 = rng.Find(CDate(secndDt), LookIn:=xlValues)
If Not r1 Is Nothing Then
x = r1.Address
End If
If Not r2 Is Nothing Then
y = r2.Address
End If
ws.Range(x & ":" & y).EntireRow.Select
End Sub

Since selecting rows can be done just as easily manually, I assume there is
a more comples purpose in wanting a macro to do this task.

 

[Code Numpty]

Thanks for the reply. I tried the code and get a runtime error - Method
'Range' of object'_Worksheet' failed

The debugger highlights this line

ws.Range(x & ":" & y).EntireRow.Select

(and yes it is part of greater plan with lots more happening, hence the
macro to select the rows)

 

[JLGWhiz]

The code was written on the assumption that the date were data type "Date".
If they are not, but are type "Text" it could cause a problem. You can try
removing the CDate and parentheses in the Find statements, leaving just the
inputbox variables as the search criteria. If that is not the problem, then
check for typo errors. It tested OK in the model that was based on data in
the original posting.

 

[JLGWhiz]

Or, you can just format column A as Date. Should work that way unless you
have other dependents or precedents that rely on column A data as text.

 

[Code Numpty]

Thanks JLGWhiz,

Ddata type is "Date" so I removed the CDate and parentheses in the Find
statements. Rows are now selected but from the first occurrence of firstDt
to the first occurrence of secndDt. Per haps I didn't explain too well but I
need the last occurrence of secndDt and I can't work out how to select the
last one rather than the first.

Appreciate your advice.

 

[JLGWhiz]

Give this a try: I changed the direction of search for r2, so it should
search from the bottom up to find the date.

Sub selDate()
Dim lstRw As Long, ws As Worksheet
Dim r1 As Range, r2 As Range, rng As Range
Dim x As String, y As String

Set ws = ActiveSheet
lstRw = ws.Cells(Rows.Count, 1).End(xlUp).Row
firstDt = InputBox("Enter beginning date", "START DATE")
secndDt = InputBox("Enter ending date", "END DATE")
Set rng = ws.Range("A2:A" & lstRw)
Set r1 = rng.Find(firstDt, LookIn:=xlValues)
Set r2 = rng.Find(secndDt, After:=Range("A2"),
SearchDirection:=xlPrevious, LookIn:=xlValues)
If Not r1 Is Nothing Then
x = r1.Address
End If
If Not r2 Is Nothing Then
y = r2.Address
End If
ws.Range(x & ":" & y).EntireRow.Select
End Sub

 

[Code Numpty]

Thanks! To change the direction was such a simple solution.

I can try and take it further from here. I appreciate your help.

 

[Code Numpty]

Ah! Just hit a problem. What if there is no end date in the list? e.g. end
date 05/11/09 - to be safe I would need to select the next nearest e.g.
07/11/09 or 06/11/09. Is this possible?

 

[JLGWhiz]

You could change this snippet:

If Not r2 Is Nothing Then
y = r2.Address
End If

Otherwise, I think I would just put an error handler in there and have the
user select another date.

To:

If Not r2 Is Nothing Then
y = r2.Address
Else
y = x
End If

Which would select only the row for the first date. Or you could do:

If Not r2 Is Nothing Then
y = r2.Address
Else
y = Range("A" & lstRw).Address
End If

Which would select everything from the first date down.

 

[JLGWhiz]

Here is a sample of error handling.

Sub selDate()
Dim lstRw As Long, ws As Worksheet
Dim r1 As Range, r2 As Range, rng As Range
Dim x As String, y As String

Set ws = ActiveSheet
lstRw = ws.Cells(Rows.Count, 1).End(xlUp).Row
firstDt = InputBox("Enter beginning date", "START DATE")
secndDt = InputBox("Enter ending date", "END DATE")
Set rng = ws.Range("A2:A" & lstRw)
Set r1 = rng.Find(CDate(firstDt), LookIn:=xlValues)
Set r2 = rng.Find(CDate(secndDt), After:=Range("A2"),
SearchDirection:=xlPrevious, LookIn:=xlValues)
If Not r1 Is Nothing Then
x = r1.Address
Else:
GoTo ErrHandler
End If
If Not r2 Is Nothing Then
y = r2.Address
Else
GoTo ErrHandler
End If
ws.Range(x & ":" & y).EntireRow.Select
ErrHandler:
MsgBox "You have entered an invalid date. Start over", , "INVALID DATE"
End Sub

 

[JLGWhiz]

If you need more assistance on this item, start a new thread so it will
appear in the latest screen of the news reader.