If InStr(UCase(c), "SATURDAY") = 0 And ....

You don't have to use the UCase function with InStr that way...InStr has an
optional parameter to handle text casing. The above code fragment could be
done this way also...

If InStr(1, c, "Saturday", vbTextCompare) = 0 And ...

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
OOPs,

I got it the wrong way around you want to keep Saturday

Sub I_Like_Saturdays()
Dim DelRange As Range
Dim LastRowD As Long, LastrowE As Long
LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row
LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE))
For Each c In MyRange
If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)),
"SATURDAY") = 0 Then
If DelRange Is Nothing Then
Set DelRange = c.EntireRow
Else
Set DelRange = Union(DelRange, c.EntireRow)
End If
End If
Next

If Not DelRange Is Nothing Then
DelRange.Delete
End If
End Sub


Mike


"Mike H" wrote:

Hi,

I'm not sure if you want 'Saturday' in both or either of columns D & E.
Right click your sheet tab, view code and paste this in and run it and it
look for saturday in both columns

Change this
If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(,

to this
If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(,
if it's either

Sub I_Dont_Like_Saturdays()
Dim DelRange As Range
Dim LastRowD As Long, LastrowE As Long
LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row
LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE))
For Each c In MyRange
If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)),
"SATURDAY") 0 Then
If DelRange Is Nothing Then
Set DelRange = c.EntireRow
Else
Set DelRange = Union(DelRange, c.EntireRow)
End If
End If
Next

If Not DelRange Is Nothing Then
DelRange.Select
End If
End Sub



Mike


"Daren" wrote:

Hello,
I have two columns that can contain the word Saturday in text values of
any
length. The word Saturday can appear in any cell in column D and E.
Is
there a macro that I can write to delete any row that do not contain
the word
Saturday in column D and E? Thanks.