deleting selected rows
 

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.

deleting selected rows
 

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

--
Gary's Student


"Worker Bee" wrote:

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.

deleting selected rows
 

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".

"Worker Bee" wrote in message
:

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.

deleting selected rows
 

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


Worker Bee wrote:

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.

deleting selected rows
 

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



"Gary''s Student" wrote:

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

--
Gary's Student


"Worker Bee" wrote:

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.

deleting selected rows
 

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.
--
Gary's Student


"RajKohli" wrote:

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



"Gary''s Student" wrote:

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

--
Gary's Student


"Worker Bee" wrote:

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.