Delete rows with 0

[Duncan J]

Hi Folks
Need a way to delete rows if coulmn B D E contain 0
Any help
thanks
DJ

 

[R.Mitchel]

-----Original Message-----
Hi Folks,
Need a way to delete rows if coulmn B D E contain 0.
Any help?
thanks,
DJ
.

I did something similiar, my code was like this.

Sub DeletRows()
Dim myRow As Long
Row = 2
Do
If myRow > 2000 Then Exit Do
Select Case Range("N" & myRow).Value

Case "S":
myRow = myRow + 1
Case Else
Rows(myRow & ":" & myRow).Select
Selection.Delete Shift:=xlShiftUp
End Select
Loop
End Sub

I was simply deleting any rows that didn't have a "S" in
column N.

 

[Frank Kabel]

Hi
try the following (adapting
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows)
Public Sub DeleteZeroRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If cells(r,"B").value = 0 and _
cells(r,"D").value = 0 and _
cells(r,"E").value = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Ron de Bruin]

Try this one
< you must have data (or header) in row 1 to use the UsedRange example >

Sub Example1()
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = .UsedRange.Rows.Count To 1 Step -1
If .Cells(Lrow, "B").Value = 0 And _
.Cells(Lrow, "D").Value = 0 And _
.Cells(Lrow, "E").Value = 0 Then .Rows(Lrow).Delete
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


See for more examples this page
http://www.rondebruin.nl/delete.htm

 

[Duncan J]

While waiting for my post to appear I came up with this and it works, however, you could boot up a commadore computer faster than this macro will ru
With Range("A1:E50000"
.AutoFilte
.AutoFilter 2,
.AutoFilter 4,
.AutoFilter 5,
If .Columns(1).SpecialCells(xlVisible).Count > 1 The
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Delete
Shift:=xlU
End I
.AutoFilte
End With

 

[Tom Ogilvy]

Sub DeleteRows()
Dim rng As Range
Set rng = Range("B:B,D:E")
rng.Replace What:=0, Replacement:="=NA()"
On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeFormulas, _
xlErrors)
On Error GoTo 0
If Not rng1 Is Nothing Then
Set rng1 = Intersect(rng1.EntireRow, Columns(1))
rng1.EntireRow.Delete
End If
End Sub

 

[Tom Ogilvy]

All three columns in the same row have to contain zero?

--
Regards,
Tom Ogilvy

While waiting for my post to appear I came up with this and it works,

however, you could boot up a commadore computer faster than this macro will
run

 

[Tom Ogilvy]

Note, this does not demand that zeros appear in all three columns for each
row that is to be deleted - so I believe it does not fit your requirement.

 

[Duncan J]

Hi Tom, for some reason your macro deleted the entire spreadsheet.
Thanks.. Frank, however, yours took a little longer than mine to run. R. Mitchel thanks. still looking at your code.
Ron your code worked as well, however, they all seem to take about 5 minutes to run...

I'll reboot my machine and try each one again. At this point I think I'll use my code.
Thanks again... Very nice info.
DJ