Delete rows depending on cell value in range

[Rupert Bennett]

Is there a routine one could use to delete any row in a range if the cell value in a specific column meets a certain criterion? I have over 17,000 rows of data but would like to delete all rows where the value in column "D" is zero or less.
Thanks for your help
RB

 

[HalfAce]

Oh, I don't know...I've seen some of your stuff and it seems pretty respectable to me. :wink:

 

[grahamj]

Hi.
I've found this one, and it does what I need, but I'd just like an explanation of Ponsy Nob's neat range selection.

The Set rng = Range([D1], [E65536].End(xlUp)(1, 0)) bit.

What does the (1, 0) at the end do?
It's not Offset.

I've worked out that for my use I need to put (1, 3), but I still don't know why.

I'm using column E to insert the formula (without doing an insert column first), but column C has the final row in it.
So I used
Set rng = Range([E1], [C65536].End(xlUp)(1, 3))
and it works beautifully.

I'd just like to know why.

 

[Zack Barresse]

Hi grahamj,

What does the (1, 0) at the end do?
It's not Offset.

Actually it is an offset. Change it around and step through your code, you should see how your range will change with it. The only reason for the second column is to base the bottom row of the selection on the last used cell in that column.

So your selection (rng) should be from E1:E(last used row in C).

The reason Ponsy put it there was to go from the top-most cell (row 1) to the last used row in the desired column of speculation, but select the column he just inserted (D in his case). So the selection would mirror the used range, only in the next column. Make sense? Hope so. Good luck!

 

[Legacy 21301]

The (1,0) bit is the same as .Item(1,0) and the same as .Offset(0,-1)

 

[grahamj]

Thanks, Zack.
It's not quite the same as Offset(row, column).
And I haven't been able to find it in the Help.
Does it go with End? Or Range?
And I'm still not quite clear on how to choose the values.
(0, 4) can give me three columns.
(1, -1) gives me all columns.

 

[grahamj]

Thanks a lot, Ponsy Nob.
It's Item. So it doesn't need the function label.