marco to delete rows
 

I have a spreadsheet that in column 'C' there are times when the word
PENDING is in the cell instead of a date. I would like to have a macro
that looks at each of these cell in COlumn 'C' and removes the entire row if
the word PENDING in that cell.


Thanks in advance

marco to delete rows
 

AutoFilter the word PENDING, then select all the PENDING rows, go to Edit
Delete Rows

"Johnfli" wrote:

I have a spreadsheet that in column 'C' there are times when the word
PENDING is in the cell instead of a date. I would like to have a macro
that looks at each of these cell in COlumn 'C' and removes the entire row if
the word PENDING in that cell.


Thanks in advance

marco to delete rows
 

Public Sub DeleteCities()
Call DeleteByWord("PENDING")
End Sub

Sub DeleteByWord(ByVal strWord As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = Sheets("Sheet1") 'Change This
Set rngToSearch = wks.Columns("C")
Set rngFound = rngToSearch.Find(What:=strWord , _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Delete
End If
End Sub

--
HTH...

Jim Thomlinson


"Johnfli" wrote:

I have a spreadsheet that in column 'C' there are times when the word
PENDING is in the cell instead of a date. I would like to have a macro
that looks at each of these cell in COlumn 'C' and removes the entire row if
the word PENDING in that cell.


Thanks in advance