Delete Row if Cell has a 0 value

[gottimd]

I want the Macro to look in the column c6:c224, and if the cell has a zero value it deletes that row, but cycle through all the cells in c6 through c224 in doing that, not just to stop after the first one.

Can anyone help out?

 

[Von Pookie]

There may be a better way, but this seemed to do the trick for me:

Option Explicit

Sub DeleteRows()

Dim ChkRange As Range
Set ChkRange = Range("C6:C224")

Dim cell As Range

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

End Sub

 

[gottimd]

Unless I put it in here wrong, it isn't deleting the rows....See code below

Application.ScreenUpdating = False

Dim ChkRange As Range
Dim cell As Range
Dim Fullnme
Dim Filenme

Range("A6:G300").Select
Selection.clearcontents
Calculate


Sheetnme = Range("E2").Value


Filenme = Range("A2").Value
Fullnme = Range("A1").Value
       Workbooks.Open Filename:= _
        "H:\" & Fullnme
  Sheets("BSVariance").Select
  Range("A11:A145").Select
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  Sheets("BSVariance").Select
  Range("B11:B145").Select
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("B6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  Sheets("BSVariance").Select
  Range("G11:G145").Select
  Application.CutCopyMode = False
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("C6").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  Range("L11:L145").Select
  Application.CutCopyMode = False
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Range("A5").Select
  Windows(Filenme).Activate
  Sheets("PLVariance").Select
  Range("A11:A94").Select
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("a141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  Sheets("PLVariance").Select
  Range("B11:B94").Select
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("B141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  Sheets("PLVariance").Select
  Range("G11:G94").Select
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("C141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  Sheets("PLVariance").Select
  Range("L11:L94").Select
  Selection.Copy
  Windows("VarianceSummary.xls").Activate
  Range("D141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows(Filenme).Activate
  ActiveWindow.Close
  Windows("VarianceSummary.xls").Activate
  Range("a5").Select
  
  Set ChkRange = Range("C6:C224")
  For Each cell In ChkRange
    If cell = "0" Then
        cell.EntireRow.Delete
    End If
Next

Range("a5").Select
Application.ScreenUpdating = True

    
End Sub

 

[unnameable]

Hi,

I've tried using the same code, but it only seems to delete half the occurences of "0". Strange indeed!

 

[Jen in Florida]

This seems to be the exact question that I need answered. But I don't understand the "code" used in the solution. Did you get it figured out? Is there a formula or condition? Is that answer found in the solution that is listed in the thread and maybe I just can't see it?

Please help if you can - thanks!

Jennifer

 

[shades]

Just a thought. Are you sure that all the values are in fact 0, and not text that looks like number?

 

[unnameable]

In fact...it misses every other row!

 

[unnameable]

Just a thought. Are you sure that all the values are in fact 0, and not text that looks like number?

I've actually changed it to look for the text "(none)", but it also acts the exact same when I have it looking for the number 0.

 

[unnameable]

I think I know what's wrong....

If, for example, it finds 0 at cell A1, it'll delete the row and then move to cell A2. It doesn't recognise that the value that was previously in A2 is now in A1, if you get what I mean.

How can this be worked around?

 

[shades]

Do the Loop in reverse order - i.e., start at the bottom. Something like the following (it will need to be fine tuned, but you should get the idea)

For each Rows.Count To 1 Step -1