Loop & Delete

J

jenna

Hii guys

Please help below

I need a procedure that will loop down column B, and delete any row with the
first five letters = "Total" and the next row.


Thanks
 
S

Sandy Mann

Jenna,

You should loop UP through the rows not down otherwise the count of the loop
will go wrong.

Try something like:

Sub DeleteIt()
Dim Starter
Dim c As Long


Starter = Cells(Rows.Count, 1).End(xlUp).Row

For c = Starter To 1 Step -1
'Change the 1 in Cells(c, 1) to the number of the column that yo want to
check
If Left(Cells(c, 1).Value, 5) = "Total" Then
Range(Cells(c, 1), Cells(c + 1, 1)).EntireRow.Delete
End If
Next c

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
S

Sandy Mann

jenna said:
Do i have to place my cursor at the bottom of the column?
Click to expand...
You don't have to place the cursor any where in particular

My apologies, I failed to notice that you asked the procedure to look at
Column B - I really should learn how to read!

Use the Macro:

Sub DeleteIt()
Dim Starter
Dim c As Long

Starter = Cells(Rows.Count, 2).End(xlUp).Row

For c = Starter To 1 Step -1
If Left(Cells(c, 2).Value, 5) = "Total" Then
Range(Cells(c, 2), Cells(c + 1, 2)).EntireRow.Delete
End If
Next c

End Sub


In this line:
Starter = Cells(Rows.Count, 2).End(xlUp).Row
the row number of the last filled cell in column 2 (ie Column B) is loaded
in the the variable *Starter*

this number is then used as the start of the loop
For c = Starter To 1 Step -1
which then steps UP through the column looking for a cell with *Total* in
it.
Also how do i delete the previous row too
Click to expand...

You originally said:
first five letters = "Total" and the next row
Click to expand...

the range in:
Range(Cells(c, 2), Cells(c + 1, 2)).EntireRow.Delete

is the row in which *Total* was found *and* the row below it
ie Cells(c, 2) is the cell in which *Total* was found and Cells(c + 1, 2) is
the next cell down (ie row c+1)

If you do want the *previous row* to be deleted then change the line then
post back again.
..

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
Top