deleting rows with zeros

[Shaolin]

if a row has a zero in each cell from column A to K I want to delete it. How can I do that?

[Jacob Hilderbrand]

Try this macro.


Option Explicit

Sub DelRows()
Dim i As Long
Dim LastRow As Long
Dim Rng As Range
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 1 Step -1
Set Rng = Range("A" & i & ":K" & i)
If Application.WorksheetFunction.Sum(Rng) = 0 Then
Rng.EntireRow.Delete
End If
Next i
End Sub

[Zack Barresse]

Hi,

This works for me ...


Option Explicit

Sub DeleteAllZeroRows()
Dim lngRow As Long, i As Long
lngRow = Range("A65536").End(xlUp).Row
For i = lngRow To 1 Step -1
If Application.WorksheetFunction.CountIf( _
Range("A" & i & ":K" & i), 0) = 11 Then Rows(i).Delete
Next
End Sub

[Shaolin]

thanks pple

and if I wanted to delete blanks in column K, I would do the following right . . .


Sub DeleteAllZeroRows()
Dim lngRow As Long, i As Long
lngRow = Range("A65536").End(xlUp).Row
For i = lngRow To 1 Step -1
If Application.WorksheetFunction.CountIf( _
Range("K:K" & i), NULL) = 11 Then Rows(i).Delete
Next
End Sub

[Shaolin]

what if I want to delete the rows that have "0," "N," or blank (NULL) in column K, in rows 3 to the bottom of the spreadsheet?

[Jacob Hilderbrand]

Try this:



Option Explicit

Sub DelRows()
Dim i As Long
Dim LastRow As Long
LastRow = Range("K65536").End(xlUp).Row
For i = LastRow To 1 Step -1
If Range("K" & i).Value = 0 Or _
Range("K" & i).Value = "" Or _
Range("K" & i).Text = "N" Then
Range("K" & i).EntireRow.Delete
End If
Next i
End Sub

[Zack Barresse]

Don't use NULL, use vbNullString.

And if you wanted to shorten it up a little bit, you could use ...


Sub DeleteAllZeroRows()
Dim i As Long
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":K" & i), _
vbNullString) = 11 Then Rows(i).Delete
Next
End Sub

Another method might look like this ...


Option Explicit
Sub DeleteAllZeroRows()
Dim r As Range, d As Range
Application.DisplayAlerts = False
Set r = Range("K1:K" & Range("K65536").End(xlUp).Row)
Set d = Range("K2:K" & Range("K65536").End(xlUp).Row)
r.AutoFilter 1, "0", xlOr, "N"
Intersect(d, d.SpecialCells(xlCellTypeVisible)).Delete
r.AutoFilter 1, vbNullString
Intersect(d, d.SpecialCells(xlCellTypeVisible)).Delete
ActiveSheet.Cells.AutoFilter
Application.DisplayAlerts = True
End Sub

[brettdj]

A For loop could be a little lengthy if you had lots of rows. A quick approach uses AutoFilter in conjunction with a formula, as per
http://www.vbaexpress.com/kb/getarticle.php?kb_id=371

Cheers

Dave




Sub KillRows()
Dim Myrange As Range
'test that user has not used column IV
If Application.CountA(Range("IV:IV")) > 0 Then
MsgBox "There are no spare columns. Macro will exit", vbCritical
Exit Sub
End If
Rows("1:1").EntireRow.Insert
Range("a1") = "dummy"
Set Myrange = Range(Cells(1, "A"), Cells(65536, "A").End(xlUp))
Application.ScreenUpdating = False
If Myrange Is Nothing Then Exit Sub
With Myrange.Offset(0, Range("K1").Column)
.EntireColumn.Insert
.Offset(0, -1).FormulaR1C1 = "=COUNTIF(RC[-11]:RC[-1],""0"")=11"
.Offset(0, -1).AutoFilter Field:=1, Criteria1:="true"
If .Offset(0, -1).Cells.Count > 0 Then .EntireRow.Delete
.Offset(0, -1).EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub