Quote:Vba loop optimization

Best loop optimization is no loop, Excel knows number of used rows of sheet.

VB

Last_Row = ActiveSheet.UsedRange.Rows.Count

See more:

I need to copy a row of formulas in 13 cells to a second spreadsheet in he same workbook. I know loops are inefficient and this is clearly an example!

Is there a different way to go here? I inactivate screen updating and calculations, but that doesn't help much. I do limit the number of rows needed by counting them in the second sheet and limiting the copying to that amount of rows.

I expect 1000-2000 rows, depending on data. 500 rows is greater than 4 minutes.

Any ideas?

Thanks,

Sleeper

**What I have tried:**

Is there a different way to go here? I inactivate screen updating and calculations, but that doesn't help much. I do limit the number of rows needed by counting them in the second sheet and limiting the copying to that amount of rows.

I expect 1000-2000 rows, depending on data. 500 rows is greater than 4 minutes.

Any ideas?

Thanks,

Sleeper

VBScript

<pre>Sub Find_Last_Row() Dim repeatCopy As Integer Dim Last_Row As Long On Error Resume Next Last_Row = Cells.Find(What:="*", After:=Range("A1"), _ LookAt:=xlPart, LookIn:=xlFormulas, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 MsgBox "Last Row: " & Last_Row 'Copy Header to Row 1 Sheets("fmulas").Range("Y1:AI1").Copy Destination:=Sheets("data").Range("Y1:AI1") Application.ScreenUpdating = False Application.Calculation = xlManual 'temp******** Last_Row = 500 For repeatCopy = 2 To Last_Row 'set Range to formula's corresponding Column 'Start in Row 2 Sheets("fmulas").Range("Y2:AI2").Copy Destination:=Sheets("data").Range("Y2:AI" & repeatCopy) Next repeatCopy Application.ScreenUpdating = True Calculate Application.Calculation = xlAutomatic End Sub

Comments

Quote:Vba loop optimization

Best loop optimization is no loop, Excel knows number of used rows of sheet.

VB

Last_Row = ActiveSheet.UsedRange.Rows.Count

Permalink

Share this answer

Be aware that sometimes

There are ways to reset that, that usually involve having to save the file. But in VBA you can ensure that it is always reset by referencing the UseRange at the start of your sub

After that, instead of using loops you should treat the

`UsedRange `

can be overstated. To demonstrate this use Ctrl-End in your worksheet to "find" the last cell used. Now type something in to a cell lower and to the right. Delete what you have just typed in, then select cell A1. Now use Ctrl-End again - Excel will (usually) take you the now empty cell that you just typed into and cleared out.There are ways to reset that, that usually involve having to save the file. But in VBA you can ensure that it is always reset by referencing the UseRange at the start of your sub

VB

ActiveSheet.UsedRange

After that, instead of using loops you should treat the

`Range`

as a set, and there is no need to use .Copy, you can just assign the formulae - text (i.e. anything that is not really a formula) will still be "copied" over. E.g. here is a sample module VB

Option Explicit Sub Demo() With ThisWorkbook.Sheets(1) 'This will ensure the UsedRange is correctly reset .UsedRange 'The number of rows and columns are properties of UsedRange Dim r As Long, c As Long r = .UsedRange.Rows.Count c = .UsedRange.Columns.Count '"Find" the address of the last cell used Dim lastcell As Range Set lastcell = .Range("A1").Offset(r, c - 1) 'Define the source and the target and copy over the formulae Dim source As Range, target As Range Set source = .Range("A1:" & lastcell.Address) Set target = ThisWorkbook.Sheets(2).Range(source.Address) target.Formula = source.Formula End With 'Alternatively - but only if you want to copy EVERYTHING Set source = ThisWorkbook.Sheets(1).UsedRange Set target = ThisWorkbook.Sheets(3).Range(source.Address) target.Formula = source.Formula End Sub

Permalink

Share this answer

Comments

Sleeper 11888211
1-Jul-22 18:57pm

@Chill60-

I looked at your code and realized the code transfers all the data in the source to target. What I have is a single row of target formulas Y2 through AI2. A second worksheet has data in a variable range, say A1:X1000. Each target row needs the formula row transferred from the source Y2:AI2, down the rows to the Last_Row, meaning one row of formulas for each row of data. I do this by looping through a copy/paste of source's single row, copied into each row of target. I test for the end row, so it is exactly the number of rows (copies) I need. I disable calculation and screen updates before the loops, but it is still slow. (sorry if duplicate. It seemed to hav frozen) S.

I looked at your code and realized the code transfers all the data in the source to target. What I have is a single row of target formulas Y2 through AI2. A second worksheet has data in a variable range, say A1:X1000. Each target row needs the formula row transferred from the source Y2:AI2, down the rows to the Last_Row, meaning one row of formulas for each row of data. I do this by looping through a copy/paste of source's single row, copied into each row of target. I test for the end row, so it is exactly the number of rows (copies) I need. I disable calculation and screen updates before the loops, but it is still slow. (sorry if duplicate. It seemed to hav frozen) S.

CHill60
2-Jul-22 3:49am

Nope - it copies the formulas. Just adjust the ranges to reflect the columns you want and use the technique inside the "with" not the "alternatively..." bit

Sleeper 11888211
5-Jul-22 10:23am

I have this working, though I am partially hard coding the range. I do find the last row needed by code, so that's the hard part. I am trying to see what I have wrong from your code.

Aside from that, how do I maintain the source's formatting?

Thanks,

Paul

Aside from that, how do I maintain the source's formatting?

Thanks,

Paul

CHill60
5-Jul-22 12:46pm

Keep formatting in questions here? When you paste stuff in you should get a pop-up menu. Iusually use "Paste as is" then select all the code and use the formatting tool "Code" at the top of the input box. You will see a list of languages appear

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900

Btw. UsedRange returns the number of columns and rows