Deletion of rows where a value is satisfied

L

Larry Wallis

I have a spreadsheet with 3 colums. Columns are headed Part Number,
Description and Qty.

What I would like to do is delete all rows where the qty value is zero.

I can't do this using auto filter as I have some small sub headings within
the list and want them to stay visible.

Any ideas please?

Many thanks.

Larry.
 
B

Bob Phillips

Sub DeleteRows()
Dim cLastRow As Long
Dim i As Long

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = cLastRow To 1 Step -1
if Cells(i,"C").Value = 0 Then
Cells(i,"C").EntireRow.Delete
End If
Next i
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

See response in .excel, and please refrain from multi-posting.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
L

Larry Wallis

See response in .excel, and please refrain from multi-posting.
Click to expand...

Sorry Bob. Thought it was OK to multi-post as long as not to more than
three groups.

Wasn't sure whether this (depending on the answer) may be covered in
various groups.

Larry.
 
T

Tom Ogilvy

Sub DD()
Dim rng as Range, i as Long
set rng = cells(rows.count,3).End(xlup)
for i = rng.row to 2 step - 1
if not isempty(cells(i,3)) then
if cells(i,3).Value = 0 then
cells(i,3).EntireRow.Delete
end if
end if
Next
end Sub
 
B

Bob Phillips

Larry,

Cross-post, that is, include all groups in the same post. When we read from
one, they all get read then.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Larry,

Seems to be me that is the problem not you. Just re-looked at your OP, and
that is exactly what you did. I seem to be getting problems, earlier I
couldn't see my reply, then I swear I saw a response from Jason Morin that I
can't see now. My only excuse is that I got confused by it all :-(

Apologies again for casting doubt on you (hope my original answer helped|).

Bob
 
L

Larry Wallis

Larry,

Seems to be me that is the problem not you. Just re-looked at your OP, and
that is exactly what you did. I seem to be getting problems, earlier I
couldn't see my reply, then I swear I saw a response from Jason Morin that I
can't see now. My only excuse is that I got confused by it all :-(

Apologies again for casting doubt on you (hope my original answer helped|).

Bob
Click to expand...

No problem at all Bob. Yes thanx, original answer was just what I was
looking for.

Thanx Bob.

Larry.
 
L

Larry Wallis

Sub DD()
Dim rng as Range, i as Long
set rng = cells(rows.count,3).End(xlup)
for i = rng.row to 2 step - 1
if not isempty(cells(i,3)) then
if cells(i,3).Value = 0 then
cells(i,3).EntireRow.Delete
end if
end if
Next
end Sub
Click to expand...

Thanks Tom.

Larry.
 
Top