Trying to make my VBA run a little bit faster

0 votes

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.

Jan 7, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
answered Jan 7, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

VBA code to select only a table. I am getting a Run-time error '1004'; Method 'Range' of object'_Global' failed

No copy/paste, just direct assignment use.Value Sub Final_Report() ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

How to return a result from a VBA function

You must associate the value with the ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
1 answer

Is there a faster way to delete table rows so my script doesn't take hours to run?

This is for your rowAddressToRemove variable. So instead of ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

I want to make Excel read a value in Calc and copy it to my sheet in Excel

Here is the sample code that will allow ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,720 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP