bdsii
05-15-2013, 02:43 PM
Hello All.....I have a question that is probably pretty easy for ya'll but is driving me nuts :banghead:
I need to use a For loop and the stop counter should be a variable based on the number of total rows with data currently in the spreadsheet. Inside the For loop, I am inserting one line based on criteria in specific cells but it seems that my stop counter is not also incrementing by that inserted row. It seems that once the stop counter is set by going through the For Loop the first time, it cannot be changed ?
I am including a snippet of my code below showing how I recalculate the total number of rows. i could be handling this all wrong, so any help would be appreciated.
the rest of the code works as expected, it just will not cycle through all the lines because of the inserted rows throwing off the total row count used in the FOR loop.
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
For i = 2 To totalrows Step 1
Range("A2").Select
currentrow = ActiveCell.Row
If IsEmpty(Range("L" & currentrow)) Then
' Do Nothing
Else
Rows(currentrow + 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F" & currentrow + 1).Value = Range("L" & currentrow).Value
'next line should reset totalrows used in the FOR loop to account for the inserted line
totalrows = ActiveSheet.UsedRange.Rows.Count
' tried using totalrows = totalrows + 1 to account for the inserted line but that did not work either
End If
Next i
:help
thanks in advance :-)
I need to use a For loop and the stop counter should be a variable based on the number of total rows with data currently in the spreadsheet. Inside the For loop, I am inserting one line based on criteria in specific cells but it seems that my stop counter is not also incrementing by that inserted row. It seems that once the stop counter is set by going through the For Loop the first time, it cannot be changed ?
I am including a snippet of my code below showing how I recalculate the total number of rows. i could be handling this all wrong, so any help would be appreciated.
the rest of the code works as expected, it just will not cycle through all the lines because of the inserted rows throwing off the total row count used in the FOR loop.
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
For i = 2 To totalrows Step 1
Range("A2").Select
currentrow = ActiveCell.Row
If IsEmpty(Range("L" & currentrow)) Then
' Do Nothing
Else
Rows(currentrow + 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F" & currentrow + 1).Value = Range("L" & currentrow).Value
'next line should reset totalrows used in the FOR loop to account for the inserted line
totalrows = ActiveSheet.UsedRange.Rows.Count
' tried using totalrows = totalrows + 1 to account for the inserted line but that did not work either
End If
Next i
:help
thanks in advance :-)