deleting selected rows

W

Worker Bee

I've got a workbook filled with worksheets containing 4 columns of
data each. I'd like to delete all of the rows (in each worksheet)
whose column B, C, and D cells contain the value "0". I am guessing
this is something that can be accomplished with nested loops, but I am
terribly rusty at VB. Can I get a push in the right direction?

Thanks much.
 
G

Gary''s Student

Enter and run this small macro:

Sub delete_some_rows()

Dim r As Range, j As Long

Set r = ActiveSheet.UsedRange
j = r.Rows.Count + r.Row
Set rdel = Cells(j, "A")
For i = 1 To j - 1
v1 = Cells(i, "B").Value
v2 = Cells(i, "C").Value
v3 = Cells(i, "D").Value
If v1 = 0 And v2 = 0 And v3 = 0 Then
Set rdel = Union(rdel, Cells(i, "A"))
End If
Next

rdel.EntireRow.Delete

End Sub
 
J

JMay

The easiest way - use the Data -Auto-filter feature
Select using the "0" and Delete all visible rows;
Might take several steps, but "it's easy".
 
L

Lori

With the columns selected choose Edit > Find: 0 and click Find All then
press Ctrl+A to select all cells and Entire > Row > Delete.
 
R

RajKohli

Both Gary's and JMay suggestion were great. But I will prefer JMay because
Macros can not be undo. Am I right Gary?
 
G

Gary''s Student

You are absolutely correct. Because AutoFilter only hides rows instead of
deleting them, you can un-do with only the click of the mouse.

The other great advantage of AutoFilter is, that by inserting a helper
column, you can hide rows based upon any criteria you choose.
 
Top