Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Rows (97:SR2)

    I'm having an issue with deleting rows in the following code:

    Sub test()
    For Each oCell In Range("C4:C1011")
    If oCell = 0 Then oCell.Rows.Delete Shift:=xlUp
    Next oCell
    End Sub

    My goal is to delete every row in the range where a cell value is zero. The code (crude as it may be) has problems if there are consecutive cells in the range (example:C10, C11, C12) that contain a zero. I'm still left with a row that contains a zero.

    I know I'm missing something obvious.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Rows (97:SR2)

    If you're deleting rows, you must loop backwards to avoid confusion:

    Sub test()
    Dim lngRowCount As Long
    Dim i As Long
    lngRowCount = Range("C4:C1011").Rows.Count
    For i = lngRowCount To 1 Step -1
    If Range("C4:C1011").Cells(i, 1) = 0 Then Range("C4:C1011").Cells(i, 1).Rows.Delete Shift:=xlUp
    Next i
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows (97:SR2)

    Hans,

    That is what I was missing: back to front.

    I modified the code to delete the entire row as opposed to only the cell and moving it up.

    For i = lngRowCount To 1 Step -1
    If Range("C4:C1011").Cells(i, 1) = 0 Then Range("C4:C1011").Cells(i, 1).EntireRow.Delete
    Next i


    Many thanks,
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •