Deleting Rows

[mooresk257]

Hi Folks,

I want to delete two rows - one row has the words "Employee Name" in the
first column, and the row before it. This for-next loop removes the row with
"Employee Name":

For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _
.Rows(Lrow).Delete
Next Lrow

Any suggestions how to delete the row with "Employee Name" and the one
before it?

Thanks!

Scott

 

[JLGWhiz]

Here is one way, without a lot of re-write.

For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _
.Rows(Lrow).Delete
.Rows(Lrow - 1).Delete
Next Lrow

 

[Don Guillett]

For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _
.Rows(Lrow-1).resize(2).Delete
Next Lrow

 

[JLGWhiz]

Don't know what I was thinking. that will delete the wrong row, since you
have already deleted the row with the criteria string. So:

For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _
.Rows(Lrow).Delete
.Rows(Lrow).Delete
Next Lrow

 

[JLGWhiz]

This also works:

For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _
.Rows(Lrow & ":" & Lrow - 1).Delete
Next Lrow

 

[Mike H]

Hi

By before it do you mean the row above, if so

Rows(lrow & ":" & lrow - 1).Delete
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Dave Peterson]

I'd use:

'not sure what firstrow is here -- not sure if you want to
'use FirstRow or FirstRow - 1
'for example: if FirstRow is 1, then FirstRow - 1 won't work

'start with the row above
For Lrow = LastRow - 1 To FirstRow - 1 Step -1
'but look at the row below
If .Cells(Lrow + 1 , "A").Value = "Employee Name" Then
.Rows(Lrow).resize(2).Delete
end if
Next Lrow

ps. I hate that single line if/then/else (especially with the line continuation
character). My eyes sometimes miss it and I hate when that happes!