Using a variable in a for-next statement RRS feed

  • Question

  • I have assigne the number of rows in a worksheet to a variable rowcounter using rowcounter = ActiveSheet.UsedRange.rows.count.  I then use the variable as an argument in a For Next statement

    For inputrow(previously defined and initialized) 2 to rowcounter


    The procedure produces an error message.  I do not get the error if a use a number rather than a variable.

    Wednesday, January 8, 2014 3:24 PM

All replies

  • Make sure both inputrow and rowcounter are dim'med as Long.

    The index that you use in a For loop can not be initialized outside the loop: this starts at 2 and goes up to rowcounter:

    For inputrow = 2 To rowcounter

    If you are initializing the first row, then use

    For inputrow = firstrow To rowcounter

    Though if you will be deleting rows, a better approach is

    For inputrow = rowcounter To 2 Step -1

    And using UsedRange.Rows.Count can be dicey, since if the used range is in rows 100:101, then the count is 2, not 101.

    Better overall would be:

        Dim lngRowCount As Long
        Dim lngRow As Long

        lngRowCount = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row
        For lngRow = 2 To lngRowCount

    Wednesday, January 8, 2014 7:19 PM
  • it would be better to loop thru the range, such as 

    dim r as range,cells as range

    set cells= ActiveSheet.UsedRange

    for each r in cells

    next r

    Friday, January 17, 2014 12:16 AM