PDA

View Full Version : [SOLVED] For Loop - increase stop counter due to inserting rows ?



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 :-)

mikerickson
05-15-2013, 03:24 PM
You could work from the bottom up.


For i = totalrows To 2 Step -1

mdmackillop
05-15-2013, 03:28 PM
When inserting or deleting rows, start at the bottom.
I'm not sure if this is just what you are after, but use this principle.
Be careful of UsedRange.Rows.Count; insert a number in a "clean" sheet in row 10, and the result will be 1. It counts the used rows, not the last used row.
Option Explicit


Sub Test()
Dim totalrows As Long, i As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
For i = totalrows To 2 Step -1
If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
' Do Nothing
Else
Rows(i + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F" & i + 1).Value = Range("L" & i).Value
End If
Next i
End Sub

bdsii
05-17-2013, 05:19 AM
Thanks guys.....that works like I need it to ! I was just hoping that there was a way to increment the stop variable in the For loop instead of having to loop backwards.

Marking this one Solved, thanks again !