×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel VBA - this is an easy one..

Excel VBA - this is an easy one..

Excel VBA - this is an easy one..

(OP)
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.

RE: Excel VBA - this is an easy one..

Sub Cleanup()
    Dim counter As Integer
    Dim curCell As Range
    For counter = 10 To 1 Step -1
        Set curCell = Worksheets("Sheet1").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

RE: Excel VBA - this is an easy one..

(OP)
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")

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")

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.

RE: Excel VBA - this is an easy one..

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close