[cool143]

Hi

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.

[JBeaucaire]

Try this:

Sub DeleteByTimeName()
'JBeaucaire  (10/16/2009)
Dim LR As Long

If Time > TimeValue("9:00 AM") And Time < TimeValue("5:00 PM") Then
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=1, Criteria1:="harry"
    LR = Range("A" & Rows.Count).End(xlUp).Row
    If LR > 1 Then Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
    Range("A1").AutoFilter
Else
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
End If

End Sub

[cool143]

Excellent asnswer JB.

Suppose if there are more than two condition like "harry","moody" and so on...

Please advice in this situation.

[JBeaucaire]

Your question #1 was clearly stated and your conditions set forth so I was able to easily answer.

Your follow-up question is not clear as to what you're after.

[cool143]

Thanks JB for your response.


Condition is as below:
If the system time is between 9:00 a.m to 5:00 p.m and name is "harry" and "Tom" and so on..

In question#1, I have mentioned only one condition, i.e if the name "harry" exist I wanted to delete the row. Suppose same case, if there are more than one name. How we can put the condition.

[JBeaucaire]

Try this version:

Sub DeleteByTimeName()
'JBeaucaire  (10/16/2009)
Dim LR As Long, MyStr As Long, MyStrings
MyStrings = Array("harry", "tom")

If Time > TimeValue("9:00 AM") And Time < TimeValue("5:00 PM") Then
    Range("A1").AutoFilter
    For MyStr = 0 To UBound(MyStrings)
        Range("A1").AutoFilter Field:=1, Criteria1:=MyStrings(MyStr)
        LR = Range("A" & Rows.Count).End(xlUp).Row
        If LR > 1 Then Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
    Next MyStr
    Range("A1").AutoFilter
Else
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
End If

End Sub

[cool143]

Excellent JB. I will run that code and let u know.