Deleting Rows with specific values in Column

Satamanster

New Member
Joined
Feb 23, 2019
Messages
3
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:

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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,881
Office Version
  1. 2013
Platform
  1. Windows
When deleting rows you have to work from the bottom up:

I would use something like this:

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
I know it's only a small data set, but no loop required


Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,881
Office Version
  1. 2013
Platform
  1. Windows
Now your original post used 81 as lastrow

This script uses Lastrow as Lastrow

Code:
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

New Member
Joined
Feb 23, 2019
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,881
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
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 !
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top