Deleting Rows with specific values in Column

[Satamanster]

Good morning everyone, let me start by saying that I've looked up for the issue for the past 4 hours and couldn't really find a solution... I tried everything but nothing works. I'm a newbie so please go easy on me...

I have this Worksheet with values on Rows 1-80 and Columns A-C.

It's as straightforward as I'm describing on title...

I want to delete all rows that have a 0 on C

Here is my code:

Dim c As Range
    For Each c In sht1.Range("C1:C81").Cells
        If c.Value = "0" Then c.EntireRow.Delete
    Next c

The weird thing about it is that it deletes some 0's. But leave others... I seriously don't get it. Can you please help? Thanks.

 

[My Aswer Is This]

When deleting rows you have to work from the bottom up:

I would use something like this:

Sub Cells_Dealete()
'Modified  2/25/2019  1:44:48 AM  EST
Dim i As Long
    For i = 81 To 1 Step -1
        If Cells(i, "C").Value = 0 Then Rows(i).Delete
    Next
End Sub

 

[Michael M]

I know it's only a small data set, but no loop required

Sub DeleteRows()
  With Range("C1:C81")
    .Replace "0", "#N/A", xlWhole, , False, , False, False
    Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[My Aswer Is This]

Now your original post used 81 as lastrow

This script uses Lastrow as Lastrow

Sub Cells_Dealete()
'Modified  2/25/2019  1:54:28 AM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Dim i As Long
    For i = Lastrow To 1 Step -1
        If Cells(i, "C").Value = 0 Then Rows(i).Delete
    Next
End Sub

 

[Satamanster]

Thank you so much! It worked like a charm! I totally missed the fact that i had to start from the bottom, why is that by the way?

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

The answer to why I will leave to someone else.

 

[Michael M]

If you delete a row, everything moves up and you skip that row on the next iteration.
The code I provided doesn't need a loop and therefore will not skip rows !
AND
on a large dataset is much faster !