VBA - how to delete zero lines (reset the loop)

Comments

• Barkeeper

  July 2008

  Easy trick, if you manipulate a List, and this manipulation changes the count of items in this list, is to go backwards through the list. I'm using a similar code nearly everyday.
  
  [code]
  Public Function CheckZeroRow(ByVal argRow as long) as boolean
  
  CheckZeroRow=ActiveSheet.Cells(argRow,10)=0 And ActiveSheet.Cells(argRow,11)=0 And ActiveSheet.Cells(argRow,12)=0 And
  ActiveSheet.Cells(argRow,13)=0 And ActiveSheet.Cells(argRow,14)=0 And
  ActiveSheet.Cells(argRow,15)=0
  
  End Function
  
  Sub DeleteZeroRows()
  Dim i as Long
  
  For i=20 to 2 Step-1
  
  if CheckZeroRow(i) then ActiveSheet.Rows(i).Delete
  
  next
  
  End Sub
  [/code]
  
  : Hi,
  :
  : I would like to ask for your help to solve one of the VBA mystery.
  : I am trying to go through all the lines and delete lines that has
  : zero # in all columns.
  :
  : So far my code is as follow:
  :
  : Private Sub delete_zero_lines() 'to take out lines with zero request
  :
  : Dim i As Integer
  : myendrow=20 'say I have 20 rows to go through
  :
  :
  : For i = 2 To myendrow 'starting from row 2 to 20
  : 'in any row with column 10 to 15 equals zero, delete the row
  :
  : If ActiveSheet.Cells(i, 10) = 0 & ActiveSheet.Cells(i, 11) = 0 &
  : ActiveSheet.Cells(i, 12) = 0 & _
  : ActiveSheet.Cells(i, 13) = 0 & ActiveSheet.Cells(i, 14) = 0 &
  : ActiveSheet.Cells(i, 15) = 0 Then
  : Rows(i).Select
  : Selection.Delete Shift:=xlUp
  :
  : End If
  :
  : Next i
  :
  :
  : What I am missing is to reset "i" after it has done deleting and I
  : don't know how to do that For example, if it goes to row 2, it
  : deletes row 2 because row 2 has zero value. The program right now is
  : saying to go to row 3 which is incorrect, once row 2 got deleted,
  : row 3 got shifted to row 2. So by going straight to row 2, I am
  : missing calculating the previously row 3.
  :
  : If you can help that would be greatly appreciated.
  :
  : Thanks in advance
  :
  
  ------------------------------------------
  Only stupidity of mankind and the universe
  are infinite, but i'm not sure concerning
  the universe. A. Einstein