Delete row when zero
 

Hi
Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0
(It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help

Thank
Andy

Delete row when zero
 

Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru 6. Should start from row 7 and end at row till the data ends
Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0
(It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help

Thank
And

Delete row when zero
 

Hi Andy,

Try:

Sub Tester()
Dim i As Long
Dim LRow As Long
Dim sh As Worksheet

' Change the sheet name to your sheet name!
Set sh = ActiveWorkbook.Sheets("Sheet1")

LRow = LastRow(sh)

For i = LRow To 7 Step -1

With sh
If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _
Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then
.Cells(i, 2).EntireRow.Delete
End If
End With
Next i

End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
after:=sh.Range("A1"), _
lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


---
Regards,
Norman

"Andy" wrote in message
...
Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru
6. Should start from row 7 and end at row till the data ends.
Can some one help me with macro or vba code to delete the row when column
B,,C,D, and E all 4 have the value of 0.
(It has to be 0 in all 4 column). Its very big sheet with thousands of
rows. Please help

Thanks
Andy

Delete row when zero
 

Hi Andy,

Try:
Sub Tester()
Dim i As Long
Dim LRow As Long
Dim sh As Worksheet

' Change the sheet name to your sheet name!
Set sh = ActiveWorkbook.Sheets("Sheet1")

LRow = LastRow(sh)

For i = LRow To 7 Step -1

With sh
If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _
Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then
.Cells(i, 2).EntireRow.Delete
End If
End With
Next i

End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
after:=sh.Range("A1"), _
lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


---
Regards,
Norman

"Andy" wrote in message
...
Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru
6. Should start from row 7 and end at row till the data ends.
Can some one help me with macro or vba code to delete the row when column
B,,C,D, and E all 4 have the value of 0.
(It has to be 0 in all 4 column). Its very big sheet with thousands of
rows. Please help

Thanks
Andy

Delete row when zero
 

This

If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _
Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then

can be reduced to

If Application.Countif(.Cells(i, 2).Resize(1, 4)),0) = 4 Then


--

HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Norman Jones" wrote in message
...
Hi Andy,

Try:
Sub Tester()
Dim i As Long
Dim LRow As Long
Dim sh As Worksheet

' Change the sheet name to your sheet name!
Set sh = ActiveWorkbook.Sheets("Sheet1")

LRow = LastRow(sh)

For i = LRow To 7 Step -1

With sh
If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _
Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then
.Cells(i, 2).EntireRow.Delete
End If
End With
Next i

End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
after:=sh.Range("A1"), _
lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


---
Regards,
Norman

"Andy" wrote in message
...
Hi Sorry All, Forget to tell you I dont want this code to look row 1
thru
6. Should start from row 7 and end at row till the data ends.
Can some one help me with macro or vba code to delete the row when
column
B,,C,D, and E all 4 have the value of 0.
(It has to be 0 in all 4 column). Its very big sheet with thousands of
rows. Please help

Thanks
Andy

Delete row when zero
 

Hi
Why dont you use the Data--Filter--Autofilter option to filter out zeroes in all the 4 columns, and delete all rows which match the criteria?
Sukhjeet