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.

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.Dele te xlShiftUp
Range("A1").AutoFilter
Else
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Dele te xlShiftUp
End If

End Sub

Excellent asnswer JB.

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

Please advice in this situation.

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.

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.

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.Dele te xlShiftUp
Next MyStr
Range("A1").AutoFilter
Else
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Dele te xlShiftUp
End If

End Sub

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