Loop more efficient ?
 

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

Loop more efficient ?
 

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)

"farmer" wrote in 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

Loop more efficient ?
 

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"farmer" wrote in 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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10/06/2004

Loop more efficient ?
 

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

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"farmer" wrote in 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

Loop more efficient ?
 

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


.