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