Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and VLOOKUP - how efficient? | Excel Worksheet Functions | |||
What is more efficient | Excel Discussion (Misc queries) | |||
Which is more efficient? | Excel Programming | |||
Is there more efficient formula? | Excel Programming | |||
More efficient code | Excel Programming |