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 in Others by Kithuzzz
• 28,900 points
49 views

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 by narikkadan
• 53,520 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
• 53,520 points
120 views
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 in Others by narikkadan
• 53,520 points
84 views
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 in Others by narikkadan
• 53,520 points
38 views
0 votes
1 answer

I am trying to run following command But I end up with an error :

Hii Nishant, You are running this command inside ...READ MORE

answered Apr 6, 2020 in Others by Niroj
• 82,840 points
1,058 views
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
• 53,520 points
157 views
0 votes
1 answer

Retrieve epay.info 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
533 views
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
2,415 views
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
• 22,960 points
117 views
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 in Others by narikkadan
• 53,520 points
42 views
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
• 53,520 points
79 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP