Deleting Rows with specific values in Column

Satamanster

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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,714
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
21,286
Office Version
  1. 365
  2. 2007
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
18,714
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
5

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
18,714
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
21,286
Office Version
  1. 365
  2. 2007
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top