How to select rows containing certain date
 

Hello Guys!
I'm in big trouble. I need to select range containing certain
date value which is enterted to inputbox.

I have 3 columns of data, second column is
date. How would the script look like if I need to select from
these 3 columns range which contains date 23.09.2003 ?

For example I have date 23.09.2003 in rows 30 to 400,
then I would like to select range A30 to C400 and copy
this data to another sheet.

Pleeeeeeeaase help me,
Jessica



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How to select rows containing certain date
 

Would it be just as easy to use AutoFilter?

http://www.contextures.com/xlautofilter01.html

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hello Guys!
I'm in big trouble. I need to select range containing certain
date value which is enterted to inputbox.

I have 3 columns of data, second column is
date. How would the script look like if I need to select from
these 3 columns range which contains date 23.09.2003 ?

For example I have date 23.09.2003 in rows 30 to 400,
then I would like to select range A30 to C400 and copy
this data to another sheet.

Pleeeeeeeaase help me,
Jessica

How to select rows containing certain date
 

Thank you Paul,
but I don't want to use autofilter. I want to get the filtered
value from input box, then loop until cursor gets to cell
with value inserted to input box. Then set selection start.
Loop until it gets to the last row with inserted date value
and set selection end.

Waiting for your help,
Jessica



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How to select rows containing certain date
 

Sub copydata1()
Dim sStr As String
Dim dDate As Date
Dim lDate As Long
Dim cell As Range
Dim rng As Range
Dim rng1 As Range
sStr = InputBox("Please enter the date")
If IsDate(sStr) Then
dDate = CDate(sStr)
Else
Exit Sub
End If
lDate = CLng(dDate)
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If cell.Value2 = lDate Then
If Not rng1 Is Nothing Then
Set rng1 = Union(rng1, cell)
Else
Set rng1 = cell
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Resize(, 3).Copy Destination:=Worksheets("Sheet2").Range("A1")
End If

End Sub

--
Regards,
Tom Ogilvy


"jessica" wrote in message
...
Thank you Paul,
but I don't want to use autofilter. I want to get the filtered
value from input box, then loop until cursor gets to cell
with value inserted to input box. Then set selection start.
Loop until it gets to the last row with inserted date value
and set selection end.

Waiting for your help,
Jessica



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/