Macro

Closed
cool - Oct 16, 2009 at 10:08 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 16, 2009 at 10:29 PM
Hello,

I wanted to create an application. I have a sheet "sheet1".there are 3 columns namely name, author and date.
A B C
1 Name Author Date
2 harry Harry 7/2/2009
3 Tom Moody 8/2/2009

Condition is as below:
If the system time is between 9:00 a.m to 5:00 p.m and name is "harry", I have to delete that rows. else i wanted to delete all rows..

Please advise.

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 16, 2009 at 10:29 PM
your second condition is dangerous because you will lose the data. so better copy sheet 1 in sheet 2 and the run this macro

do this experiment only on a sample data first.

Sub test()
Dim r As Range, cfind As Range, tt
Worksheets("sheet1").Select
Set r = Range(Range("a2"), Range("A2").End(xlDown))
With r
Set cfind = .Cells.Find(what:="harry", lookat:=xlWhole)
End With
tt = TimeValue(Now)
MsgBox tt
If tt >= TimeValue("9:00 am") And tt <= TimeValue("5:00 pm") Then
cfind.EntireRow.Delete
Else
r.EntireRow.Delete
End If
End Sub
0