View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
sebastienm sebastienm is offline
external usenet poster
 
Posts: 694
Default Macro to Recognize Dates

1. Check the online help for the Select Case, you'll see that you can group
muliple case with:
Select Case ActiveCell
Case "Print", "Page", "Users", "Status"
.....
2. Any way, try the follwoing:

Sub test()
Dim rg As Range, rgDelete As Range
Dim TextValues As String
Dim IsToBeDeleted As Boolean, IsInTextValues As Boolean, IsADate As Boolean

Set rg = Range("A1")
TextValues = "Print,Page,Users,Status"


On Error Resume Next ' to prevent error when converting date
TextValues = "," & TextValues & ","

Do Until rg = ""
rg.Select
'check for text values
IsInTextValues = TextValues Like ("*," & rg.Text & ",*")
'check for a date
IsADate = (CDate(rg.Text) DateValue("01/10/1901"))
If Err < 0 Then Err.Clear
'Is to be deleted
IsToBeDeleted = IsInTextValues Or IsADate
If IsToBeDeleted Then
If rgDelete Is Nothing Then 'no range in it yet
Set rgDelete = rg
Else 'just add to it
Set rgDelete = Application.Union(rg, rgDelete)
End If
End If
'next cell
IsADate = False
IsInTextValues = False
Set rg = rg.Offset(1, 0)
Loop

'Delete at the end only
If Not rg Is Nothing Then
rg.EntireRow.Delete 'rgDelete.EntireRow.Select
End If
End Sub

I hope this helps
--
Regards,
Sébastien
<http://www.ondemandanalysis.com