Macro to select rows based on dates
 

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?

Macro to select rows based on dates
 

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" wrote in message
...
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?

Macro to select rows based on dates
 

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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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" wrote in message
...
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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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

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

"JLGWhiz" wrote:

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" wrote in message
...
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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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" wrote:

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" wrote in message
...
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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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.





"Code Numpty" wrote:

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" wrote:

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" wrote in message
...
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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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

"Code Numpty" wrote:

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" wrote:

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" wrote in message
...
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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to select rows based on dates
 

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

"Code Numpty" wrote:

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" wrote:

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" wrote in message
...
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" wrote:

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.


"JLGWhiz" wrote in message
...
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.



"Code Numpty" wrote in message
...
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" wrote:

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" wrote in message
...
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?