Excel VBA - this is an easy one..

[scroce]

OK - in column D I have 10 numbers range d1 thru d10
say like this:

2
3
4
0
8
0
7
8
0
0

All I want to do is write some code that will find all the cells that have zeros in them (which in this example is 4), and delete the entire row. (as if i did selection.entirerow.delete, or went to Edit > Delete > Entire row.

This is easy, so why can't i figure it out? Every thing i've tried hasn't worked. I'm relatively new at coding, and I think the problem is that I still have yet to really understand the object model

Would somebody be able to supply me with sample code that works. I'd be very grateful.

SteveC.

 

[RickSpr]

Sub Cleanup()
Dim counter As Integer
Dim curCell As Range
For counter = 10 To 1 Step -1
Set curCell = Worksheets("Sheet1&quot.Cells(counter, 4)
If curCell.Value = 0 Then curCell.EntireRow.Delete
Next counter
End Sub

Maybe the reason you've had trouble getting it to work is that you let the For loop count upward rather than downward. The problem with that is that, when you delete a row, the next value you want to check will shift up to the same row number as the one you deleted. To make it work, you'd have to keep the loop control variable from incrementing on the next iteration. Unfortunately, you can't adjust the loop control variable to do this (not properly, anyway). But if you do it from the top row number downward, only rows you've already looked at will get shifted, so they won't interfere with your logic. Rick Sprague

 

[scroce]

Hey Rick,

you are exactly right. I kept trying to do something like

Dim myRange as Range
Dim Cell as Object
Dim intcount as Integer

Set myRange = ("d1:d10&quot

For Each Cell in myRange
If Cell.Value = 0 Then
Cell.Activate
ActiveCell.EntireRow.Delete
intCount= intCount + 1
End If
Next Cell

...you get the general idea....

And exactly as you said, it would loop, but I believe the command to delete the entire row screwed up the counter. I tested this by commenting out the delete command, and the counter counted the right number of rows each time. This was driving me up the wall, b/c I couldn't figure out how to overcome it.

So I'll try out your suggestion - the other thing I've been fooling around with is something like:

Dim myRange as Range

Set myRange = Range("d1:d10&quot

myRange.Select
Seletion.Autofilter
Selection.Autofilter Field:=1, Criteria1="0"
Selection.EntireRow.delete

Maybe this will work. I'll post back to let you know.

 

[scroce]

Just a note:

After playing around with both the filter code and the counter code (counting backwards) I found that the counting code that you supplied was more efficient. Again, Thanks for the tip, it worked very well.

Steve C.