Delete row when zero

A

Andy

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
 
A

Andy

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
 
N

Norman Jones

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 said:
Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru
Click to expand...
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
Click to expand...
B,,C,D, and E all 4 have the value of 0.
 
N

Norman Jones

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 said:
Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru
Click to expand...
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
Click to expand...
B,,C,D, and E all 4 have the value of 0.
 
B

Bob Phillips

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)
 
S

Sukhjeet

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
 
Top