VBA Speed Improvement by Array MEthod

Question

Hi,

in my code, I am using multiple Copy Paste here.

How can I use array method to increase the speed?

How to copy paste below code now? using Array Method?

Should we use .value2 or .value to increase speed? Currently, I have multiple rows of copy paste like this in my code.

How to increase speed for copy paste using array method?

Sheets("AS").Range("D4") = Worksheets("INPUT").Range("F16")

Sheets("AS").Range("D5") = Worksheets("INPUT").Range("F17")

Sheets("AS").Range("D6") = Worksheets("INPUT").Range("F18")

Sheets("AS").Range("D7") = Worksheets("INPUT").Range("F19")

Sheets("AS").Range("D8") = Worksheets("INPUT").Range("F20")

Sheets("AS").Range("D9") = Worksheets("INPUT").Range("F21")

Sheets("AS").Range("D10") = Worksheets("INPUT").Range("F22")

Sheets("AS").Range("D11") = Worksheets("INPUT").Range("F23")

Sheets("AS").Range("D12") = Worksheets("INPUT").Range("F24")

Sheets("AS").Range("D13") = Worksheets("INPUT").Range("F25")

Sheets("AS").Range("D14") = Worksheets("INPUT").Range("F26")

Sheets("AS").Range("D15") = Worksheets("INPUT").Range("F27")

Sheets("AS").Range("D16") = Worksheets("INPUT").Range("F28")

Sheets("AS").Range("D17") = Worksheets("INPUT").Range("F29")

Sheets("AS").Range("D18") = Worksheets("INPUT").Range("F30")

Sheets("AS"). Range("D19") = Worksheets("INPUT"). Range("F31")

Also, second query is for Activate method:

Worksheets("Sheet5"). Activate

Will above activate method slow down the code?

What is the alternate solution for this?

Please advice on above points.

Answer 1

The line

Sheets("AS").Range("D4:D19") = Worksheets("INPUT").Range("F16:F31")

writes to all 16 cells in one go, instead of writing to each of the 16 cells separately, so it is more efficient.

You won't notice the difference, though, since the number of cells is small.

But if you wanted to do this for D4:D200000 and F16:F200012, the original code would be much slower.

If you really need to activate a sheet in VBA. you can use a line such as the one you mentioned:

Worksheets("Sheet5"). Activate

But why do you want to do that?

Answer 2

Hi,

Sheets("AS").Range("D4:D19") = Worksheets("INPUT").Range("F16:F31") is it faster than earlier lines?

Also i need to activate particular sheet at particular line in the code

how to do it alternatively in VBA?

Answer 3

The lines

Sheets("AS").Range("D4") = Worksheets("INPUT").Range("F16")
Sheets("AS").Range("D5") = Worksheets("INPUT").Range("F17")
Sheets("AS").Range("D6") = Worksheets("INPUT").Range("F18")
Sheets("AS").Range("D7") = Worksheets("INPUT").Range("F19")
Sheets("AS").Range("D8") = Worksheets("INPUT").Range("F20")
Sheets("AS").Range("D9") = Worksheets("INPUT").Range("F21")
Sheets("AS").Range("D10") = Worksheets("INPUT").Range("F22")
Sheets("AS").Range("D11") = Worksheets("INPUT").Range("F23")
Sheets("AS").Range("D12") = Worksheets("INPUT").Range("F24")
Sheets("AS").Range("D13") = Worksheets("INPUT").Range("F25")
Sheets("AS").Range("D14") = Worksheets("INPUT").Range("F26")
Sheets("AS").Range("D15") = Worksheets("INPUT").Range("F27")
Sheets("AS").Range("D16") = Worksheets("INPUT").Range("F28")
Sheets("AS").Range("D17") = Worksheets("INPUT").Range("F29")
Sheets("AS").Range("D18") = Worksheets("INPUT").Range("F30")
Sheets("AS"). Range("D19") = Worksheets("INPUT"). Range("F31")

can be replaced with

Sheets("AS").Range("D4:D19") = Worksheets("INPUT").Range("F16:F31")

You should only activate a sheet in VBA code if it is strictly necessary. In most situations, it can be avoided.

The same goes for selecting a cell or range.

Selecting and activating take time, so don't do it if at all possible.