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


-----Original Message-----
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


.