Alternate method 2 -

You could also implement this by passing a search range in instead of a
worksheet and column number. This solves the problem of deleting START in row
1.

With the first method, you could grab the value of .Cells(1,4), change it,
run the PROC, then change it back. Or you could trap when rngHitCell was on
row 1, etc.

BTW, is there a nice way of specifying Columns(4) except for row 1?
Something like Exclude(rng1,rng2) that returns rng1 minus what's in rng2.

Bob

Option Explicit

Public Sub DeleteRows2(rngSearch As Range, strSearch As String)
Dim rngHitCell As Range
Set rngHitCell = rngSearch.Find _
(What:=strSearch _
, LookIn:=xlFormulas _
, LookAt:=xlWhole _
, SearchOrder:=xlByRows _
, SearchDirection:=xlNext _
, MatchCase:=False _
, SearchFormat:=False _
)

While Not rngHitCell Is Nothing
rngSearch.Parent.Rows(rngHitCell.Row).Delete
Set rngHitCell = rngSearch.FindNext
Wend
End Sub

Public Sub TestDeleteRows2()
With ThisWorkbook.Worksheets(1)
DeleteRows2 rngSearch:=.Range(.Cells(2, 4), .Cells(65536, 4)),
strSearch:="START"
End With
End Sub


"INTP56" wrote:

Alternate method:

Option Explicit

Public Sub DeleteRows(WS As Worksheet, strSearch As String, intColumnNumber
As Integer)
Dim rngHitCell As Range
Set rngHitCell = WS.Columns(intColumnNumber).Find _
(What:=strSearch _
, LookIn:=xlFormulas _
, LookAt:=xlWhole _
, SearchOrder:=xlByRows _
, SearchDirection:=xlNext _
, MatchCase:=False _
, SearchFormat:=False _
)

While Not rngHitCell Is Nothing
WS.Rows(rngHitCell.Row).Delete
Set rngHitCell = WS.Columns(intColumnNumber).FindNext
Wend
End Sub

Public Sub TestDeleterows()
DeleteRows WS:=ThisWorkbook.Worksheets(1), strSearch:="START",
intColumnNumber:=4
End Sub

Bob

"Mike R." wrote:

Hi -
this seems like a pretty easy loop statement. What am I doing wrong? I am
trying to see if a value in a row equal's the word START and if so, delete
that row.
Help..

irows = ActiveSheet.UsedRange.Rows.Count
For iloop = 2 To irows
If Cells(iloop, "D").Value = "START" Then Cells(iloop,
"D").EntireRow.Delete
Next iloop