Delete rows if cell in column H is zero

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,016
Data is in rows 2:3001

If the value in column H of each row is zero I need the entire row deleting.

Code:
Sub DeleteRows()

Dim ChkRange As Range
Set ChkRange = Range("H2:H3001")

Dim cell As Range

For Each cell In ChkRange
    If cell = "0" Then
        cell.EntireRow.Delete
    End If
Next

End Sub

I thought the above would work but it seems to only remove a couple of rows, where most of them should be deleted!!

TIA
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Are the zeros values, or the result of a formula?
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,016
Initially on another tab calculated using a formula, but paste special-values into the above spreadsheet
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
In that case, try
Code:
Sub DeleteZeros()

With Range("H2:H3001")
   .Replace 0, "=XXX", xlWhole, , , , False, False
   On Error Resume Next
   .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   On Error GoTo 0
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,621
Messages
5,523,947
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top