Trying to make my VBA run a little bit faster

Question

Since I'm new to VBA, my codes are frequently quite slow or inefficient.

When the user presses a button in one of my programs, I have cells in the sheet that must be filled. The values vary based on the button, but the idea is the same.

So I did this monstrosity:

Cells((Range("namedrange").Row + 5), 1).Value = ThisWorkbook.Sheets(5).Cells(4, 7).Value
Cells((Range("namedrange").Row + 5), 3).Value = ThisWorkbook.Sheets(5).Cells(4, 8).Value
Cells((Range("namedrange").Row + 5), 5).Value = ThisWorkbook.Sheets(5).Cells(4, 9).Value
Cells((Range("namedrange").Row + 5), 8).Value = ThisWorkbook.Sheets(5).Cells(4, 10).Value
Cells((Range("namedrange").Row + 5) + 1, 1).Value = ThisWorkbook.Sheets(5).Cells(5, 7).Value
Cells((Range("namedrange").Row + 5) + 1, 3).Value = ThisWorkbook.Sheets(5).Cells(5, 8).Value
Cells((Range("namedrange").Row + 5) + 1, 5).Value = ThisWorkbook.Sheets(5).Cells(5, 9).Value
Cells((Range("namedrange").Row + 5) + 1, 8).Value = ThisWorkbook.Sheets(5).Cells(5, 10).Value

But later changed to:

    With Range("namedrange")
        .Offset(5).Columns(1).Value = ThisWorkbook.Sheets(3).Cells(4, 7).Value
        .Offset(5).Columns(3).Value = ThisWorkbook.Sheets(3).Cells(4, 8).Value
        .Offset(5).Columns(5).Value = ThisWorkbook.Sheets(3).Cells(4, 9).Value
        .Offset(5).Columns(8).Value = ThisWorkbook.Sheets(3).Cells(4, 10).Value
        .Offset(6).Columns(1).Value = ThisWorkbook.Sheets(3).Cells(5, 7).Value
        .Offset(6).Columns(3).Value = ThisWorkbook.Sheets(3).Cells(5, 8).Value
        .Offset(6).Columns(5).Value = ThisWorkbook.Sheets(3).Cells(5, 9).Value
        .Offset(6).Columns(8).Value = ThisWorkbook.Sheets(3).Cells(5, 10).Value
    End With

This is a little quicker, but I believe it is still not at its best. And if there is a method to make it cleaner or more elegant, that would be great to know. It should be noticed that there are gaps in the columns; for example, it begins in the first column but jumps to the third, then the fifth, and finally the eighth.

I'm just looking for a method to improve the code faster or clearer because it works but is slow.

narikkadan · Answer 1 · Jan 7, 2023

Using Variables

In regards to efficiency, that's about it: you're using the most efficient way to copy values from one cell to another aka copying by assignment.
If you want it to be more flexible, maintainable, and readable(?), here are some ideas.
Additionally, you can move the remaining magic numbers and text to constants at the beginning of the code or even use the constants as arguments.

Sub CopyValues()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Specify the worksheet if you know it.
    'Dim dnrg As Range: Set dnrg = wb.Sheets("Sheet1").Range("NamedRange")
    ' Otherwise, make sure the workbook is active.
    If Not wb Is ActiveWorkbook Then wb.Activate
    Dim dnrg As Range: Set dnrg = Range("NamedRange")
    
    Dim drg As Range: Set drg = dnrg.Range("A1,C1,E1,H1").Offset(5)
    Dim cCount As Long: cCount = drg.Cells.Count
    
    ' If you know the tab name, use it instead of the index (3).
    Dim sws As Worksheet: Set sws = wb.Sheets(3)
    Dim srg As Range: Set srg = sws.Range("G4").Resize(, cCount)
    
    Dim r As Long, c As Long
        
    For r = 0 To 1
        For c = 1 To cCount
            drg.Offset(r).Cells(c).Value = srg.Offset(r).Cells(c).Value
        Next c
    Next r

End Sub