Delete Rows with specific values

lynchoftawa

New Member
Joined
Mar 27, 2006
Messages
7
Hi there

I want to be able to delete rows when the value in column C has a value of zero.

I have three columns of data, column C is the last column;

example

#z001 xyz 100.0
#z002 abc 0.0

etc
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
have you tried autofilter? filter 0.0 values in column C then delete all the results.
 

lynchoftawa

New Member
Joined
Mar 27, 2006
Messages
7
Thanks

I tried that, the only problem is that number of rows will sometimes change.

The end result needs to be a .csv or .txt file so it can be uploaded into another application. So this function currently sits in the middle of a macro that sorts, tries to delete rows with zero values, and then formats the file into a .csv file.

Everything else works except the deletion of zero value rows.

Cheers
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
record a macro then do the aufiltering process to obtain the codes.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,220
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

lynchoftawa

Welcome to the Mr Excel board!

You could see if this code is suitable:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteRows()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Row <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    LastRow = Range("C65536").End(xlUp).Row
    <SPAN style="color:#00007F">For</SPAN> Row = LastRow <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">If</SPAN> Cells(Row, 3).Value = 0 <SPAN style="color:#00007F">Then</SPAN>
            Cells(Row, 3).EntireRow.Delete
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> Row
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If you do not want to delete rows where column C is blank, then change the appropriate line above to:
If Cells(Row, 3).Value = 0 And Cells(Row, 3).Value <> "" Then
 

lynchoftawa

New Member
Joined
Mar 27, 2006
Messages
7
Thanks heaps, that worked a treat.

I am obviously new this coding stuff, and very interested to learn how it all works. I understand most of the code, just a little unsure of what the lines;

Application.ScreenUpdating
Application.EnableEvents

actually do, if you had sometime, would you or anybody else be able to explain for me.

Many thanks

lynchoftawa :biggrin:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,220
Office Version
  1. 365
Platform
  1. Windows
Thanks heaps, that worked a treat.

I am obviously new this coding stuff, and very interested to learn how it all works. I understand most of the code, just a little unsure of what the lines;

Application.ScreenUpdating
Application.EnableEvents

actually do, if you had sometime, would you or anybody else be able to explain for me.

Many thanks

lynchoftawa :biggrin:
Application.ScreenUpdating = False
This stops Excel updating what appears on the screen until it is set back to True at the end of the process. This has two advantages:
1. If it were not done, you would see a lot of flickering (particularly if you have a lot of rows in your sheet that need deletion) as the code runs.
2. The code runs faster if it does not have to stop and update the screeb display after every change.

To see what happens, temporarily 'Comment Out' that line of code by preceding it with a ' (or change the False to True).

Application.EnableEvents = False
This may not be so important in this code but stops any other code that might be triggered by a change in your sheet from running every time this code deletes a row. It is vital in some instances.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,651
Messages
5,660,127
Members
418,550
Latest member
kp_

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