Macro is too slow

Question

I'm fairly new to programming. And I'm not sure why the macro is running too slow (excel file is not responding).

Have tried to simplify this

Worksheets("Sheet2").Select
        Range("S1", Range("S1").End(xlDown)).Copy
        Worksheets("Sheet1").Select
        Range("K10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks:=False, Transpose:=False

to this

Worksheets("Sheet2").Range("S1", Range("S1").End(xlDown)).Copy
Worksheets("Sheet1").Range("K10").PasteSpecial

but it's not pasting the values. I don't know what I did wrong.

Please do not post code [as screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557), show it as [formatted text](https://meta.stackoverflow.com/a/251362) — , Oct 01 '19 at 07:27
Also see here: [Avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Dean, Oct 01 '19 at 07:39
Perhaps best to post your entire code on [code review](https://codereview.stackexchange.com/) instead. The site is specifically meant to help people improve code and make it more efficient. — Plutian, Oct 01 '19 at 08:04
If you are only pasting values, then why use copy/paste to begin with? `rng.Value = rng2.Value` is far more efficient. And even more so, grabbing all the values and placing them into an array and making modifications to the array is probably what you should be doing. Multiple calls to the worksheet object is resource intensive. Make 1 read call to the ws obj, place into a variable. Then 1 write call to the ws obj. — K.Dᴀᴠɪs, Oct 01 '19 at 08:10
@K.Davis Have tried that before but it is not reflecting the data. The source range from sheet2 are filtered on columns 1, 6 and 11 based on set criteria. Then the values on column 19 are to be copied on sheet1. It is always showing blank. Should I use If function instead of filtering the values? — Iam8Teen, Oct 01 '19 at 09:42
@a_horse_with_no_name that didn't allow me to post the question since it said that the question is mostly codes — Iam8Teen, Oct 01 '19 at 09:59

score 0 · Accepted Answer · answered Oct 01 '19 at 07:34

0

your code is probably so slow since your range is huge (up to 50000) and it has to loop through it, which takes a great amount of time. Try rewriting your either using an dynamic range, or by avoiding using the loop function.

answered Oct 01 '19 at 07:34

Walter Kloosterboer

83
1
6

Have tried to filter from the source workbook to lessen the data, from 50k it is now down to 2k. The speed improved. Thanks. – Iam8Teen Oct 01 '19 at 09:57

Macro is too slow

1 Answers1