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?

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?

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?

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?

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?

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?