Delete Rows with specific values

[lynchoftawa]

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

 

[agihcam]

have you tried autofilter? filter 0.0 values in column C then delete all the results.

 

[lynchoftawa]

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]

record a macro then do the aufiltering process to obtain the codes.

 

[Peter_SSs]

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]

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

 

[Peter_SSs]

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

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.