Vba loop optimization

Question

0.00/5 (No votes)

See more:

Excel-2016

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:

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

Posted 27-Jun-22 9:48am

Sleeper 11888211

Updated 28-Jun-22 1:15am

v2

Add a Solution

Comments

0x01AA 27-Jun-22 16:06pm

Something like that? Worksheet.UsedRange property (Excel) | Microsoft Docs[^]
Btw. UsedRange returns the number of columns and rows

2 solutions

Solution 1

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

Posted 27-Jun-22 17:04pm

Patrice T

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2022-06-28T01:15:00

Be aware that sometimes 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