Delete Row if Cell Value = 0 (Probably Easy?)

[Leila-Marie]

Hi,

I have a question (which kind of follows on from my post yesterday)
See:
http://www.mrexcel.com/forum/excel-questions/722302-function-determining-if-city-contained-list.html

Yesterday, I asked for help to find a formula for returning the value "1" or "0" depending on whether a given city was on our company's list...

This is now working really well, using "ifcount".

At the moment I'm manually deleting the rows with a "0" in Column D.

Can I get Excel to do this for me?

By doing something like "if D1=0 then delete row 1" ?

Thank you for any suggestions,

Leila-Marie

 

[VoG]

Try this macro

Sub DelD0()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("D" & i).Value = 0 Then Rows(i).Delete
Next i
End Sub

 

[Leila-Marie]

Try this macro

Sub DelD0()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("D" & i).Value = 0 Then Rows(i).Delete
Next i
End Sub

Hi Peter,

Thank you so much!

That works perfectly. I just tried it on a copy of a spreadsheet I'd already done "manually" and it came up with the exact same results - brilliant.

You guys on this forum are worth a million bucks!

Do you have a donation thingee? I'd definitely be willing to donate a few $ (or Euros, in my case) via PayPal for every "solved problem" that I post here - and I'm sure others would be too.

If funds aren't needed for running this site, maybe you could collect donations for a good cause that's somehow related to this forum? I'm sure site's like Kahn Academy <cite> </cite>would adore some $ for their great work, for example...

All the best,

Leila-Marie

 

[VoG]

Hi - you are welcome.

As far as I know there is no way of making donations but maybe you could click on a few adverts - might tempt you to buy one of Bill's books.

 

[Leila-Marie]

Is Bill the person who started this forum? I don't actually know what his books are... but I assume they're to do with Excel

I guess I could send him a PM to suggest a PayPal donation thingee?

 

[Leila-Marie]

Oh, P.S. I have "AdBlocker" on my Firefox, so I cannot see any ads...

 

[VoG]

Bill is Bill Jelen who is MrExcel.