none
Reset loop counter during the loop RRS feed

  • Question

  • Using MS Office 2007 and Vista

    I have an Excel procedure during which I step through several blocks of data arranged in rows.  In each block I have variables to define the top and bottom row.  I test the contents of a cell in each row using a For Next loop, and if it meets certain conditions I delete the row.  This requires me to reset the total number of rows in the block.  My code achieves this, but it appears that the number of the last row is set at the beginning of the loop and is not reset as the loop proceeds.  The relevant code is:

    For intCounter1 = lngNonSalesCreditsTop To lngNonSalesCreditsBottom
            Range("G" & intCounter1).Select
            If ActiveCell.MergeCells = True Then
                intMergeRowCount = Selection.Rows.Count
                Range("G" & intCounter1, "G" & intCounter1 + (intMergeRowCount - 1)).Select
                Selection.EntireRow.Delete
                lngNonSalesCreditsBottom = lngNonSalesCreditsBottom - intMergeRowCount
                intCounter1 = intCounter1 - 1
            ElseIf ActiveCell.Value <> "" Then
                ActiveCell.EntireRow.Delete
                lngNonSalesCreditsBottom = lngNonSalesCreditsBottom - 1
                intCounter1 = intCounter1 - 1
            End If
        Next intCounter1

    This successfully deletes the rows that I want to delete, but it continues down past the end of the (now modified) block into the next block because it retains the original value of the final row (lngNonSalesCreditsBottom) even though the variable value is shown as decreased in the locals window.  As an interim measure I can test for the first line of the next block (which is a named range) and do and Exit For, but it seems an inelegant way of doing it.  Is there a way to reset the end number of the loop during the loop?

    Friday, January 3, 2014 11:02 AM

Answers

  • If you want to delete rows in a loop, it's best to loop backwards.

    For efficiency, I'd avoid selecting cells.

        Application.ScreenUpdating = False
        For intCounter1 = lngNonSalesCreditsBottom To lngNonSalesCreditsTop Step -1
            If Range("G" & intCounter1).MergeCells = True Then
                With Range("G" & intCounter1).MergeArea
                    intMergeRowCount = .Rows.Count
                    .EntireRow.Delete
                End With
                intCounter1 = intCounter1 - (intMergeRowCount - 1)
            ElseIf Range("G" & intCounter1).Value <> "" Then
                Range("G" & intCounter1).EntireRow.Delete
            End If
        Next intCounter1
        Application.ScreenUpdating = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AndyColRomsey Friday, January 3, 2014 12:10 PM
    Friday, January 3, 2014 11:41 AM

All replies

  • If you want to delete rows in a loop, it's best to loop backwards.

    For efficiency, I'd avoid selecting cells.

        Application.ScreenUpdating = False
        For intCounter1 = lngNonSalesCreditsBottom To lngNonSalesCreditsTop Step -1
            If Range("G" & intCounter1).MergeCells = True Then
                With Range("G" & intCounter1).MergeArea
                    intMergeRowCount = .Rows.Count
                    .EntireRow.Delete
                End With
                intCounter1 = intCounter1 - (intMergeRowCount - 1)
            ElseIf Range("G" & intCounter1).Value <> "" Then
                Range("G" & intCounter1).EntireRow.Delete
            End If
        Next intCounter1
        Application.ScreenUpdating = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AndyColRomsey Friday, January 3, 2014 12:10 PM
    Friday, January 3, 2014 11:41 AM
  • Now, why didn't I think of that?!

    As always Hans, thanks for the reply and the useful comment about avoiding selecting the cells.

    Andy C

    Friday, January 3, 2014 12:13 PM