Help with code for deleting row containing zero in a column. 2

[pdldavis]

Hi, I cannot get this statement to work correctly. What I would like to do is examine column three for values equaling zero. I would like to then delete any rows containing a zero in that column.

Dim intRow As Integer
Dim intCol As Integer
intRow = 2
intCol = 3
Cells(intRow, intCol).Activate 'cell 3
Do Until intRow > 4000 'do this until 4001
If Val(Cells(intRow, intCol).Value) = 0 Then
Selection.EntireRow.Delete
End If
intRow = intRow + 1
Cells(intRow, intCol).Activate
Loop
End Sub

Any help would be appreciated.
Thanks, Dan

 

[petermeachem]

Are we talking grid here? Did the data come from a database? If so, then
DELETE * FROM Whatever WHERE col1 = 0 AND col2 = 0 AND col3 = 0
and the refresh the grid, will be much much better. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[CajunCenturion]

One problem with the logic is that you will skip some rows.

If inRow is 3, and you deleted that row, then what was row four is now row 3, so that when you increment intRow, you will move up a row, effectively skip what was originally row 4. I hope that made some sense.

Also if you start with 4005 rows, and you delete 10 of them, by the time you get to the end, you'll only have 3995 rows, which may result in trying to read beyond the end of the grid

I would start at 4000 and work back to 2

intRow = 4000
intCol = 3
Cells(intRow, intCol).Activate 'cell 3
Do Until intRow < 2 'do this until 2
If Val(Cells(intRow, intCol).Value) = 0 Then
Selection.EntireRow.Delete
End If
intRow = intRow + 1
Cells(intRow, intCol).Activate
Loop

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[pdldavis]

That worked perfectly! I only had to change the introw to -1
and it sailed through. Thanks for the assistance and the explanation behind it.

Dan

 

[CajunCenturion]

LOL - you're absolutely right.

intRow = intRow - 1

will work a whole lot better

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein