Loop more efficient ?

[farmer]

Hi,

I would like to use a For Next loop to go through (several) worksheets
to (among other operations) insert rows when some criteria are met.

The problem i ran into was that the "LastRow" increases when rows are
inserted. So the rows at the end of the worksheet stay the unaffected

To overcome this problem I made two loops. The first to determine how
many rows to add to the current lastRow, and the second loop tot do
the actual things I want the do.


Here is the Code:

Private Sub CommandButton2_Click()

Dim sh As Worksheet
Set sh = ActiveSheet

"Loop 1 to determine how many rows to add to the current lastRow

cnt = 1
For introw = 1 To LastRow(sh)

If Application.IsNumber(Cells(introw, 1)) Then
cnt = cnt + 1

End If

introw = introw + 1

Next introw


"Loop 2 is the actual macro to do the job

For introw = 1 To LastRow(sh) + cnt

If Application.IsNumber(Cells(introw, 1)) Then
Cells(introw, 1).EntireRow.Insert

End If

introw = introw + 1

Next introw

End Sub


I was wondering if the Code I use can be made more efficient.


Help appreciated,


Farmer

 

[Bob Phillips]

Not really clear what is happening, but I thin k you should remove the lines

introw = introw + 1

as the lopop does that itself with the Next intRow

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Just loop backwards and you'll have no problems. If you do anything that
affects the structure of the workbook, ie inserting/deleting rows, then
determine the last row from the outset and then start from there, doing whatever
you need to do, but working backwards one row at a time. This way you don't
affect the rows above and can loop through them normally, eg:-

Sub Do_Stuff()

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For RowNdx = LastRow To 1 Step -1
Blah Blah Blah...............
Next RowNdx

End Sub

 

[Earl Kiosterud]

Farmer,

Your first loop predicts how many inserts you'll make, and adjusts the
second loop accordingly. If you have to change the logic for any reason,
you have two loops to change.

You could adjust LastRow as you go. Untested.

Cnt = 0
Do
Cnt = Cnt + 1
If IsNumber(...
Insert...
LastRow = LastRow + 1
End if
Loop While Cnt < LastRow

 

[Hmmm]

sub loopLa()
range("A2").select
set a=selection
range(a,a.SpecialCells(xlCellTypeLastCell)).select
'selects the range of A2 to the last used cell in column A
for each cell in selection
if cell.value="Hello" then
cell.entirerow.insert
end if
next

end sub