Excel/Altering VBA

Advertisement


Question
Hi Tom

I have just recorded this macro.  It performs a very simple task but it is very slow because of the amount of scrolling that was involved.  

Could it be simplified?

Thanks in advance

Chris Mitchell

Sub core()
'
' core Macro
'

'
   Range("Q2:Q501").Select
   Selection.ClearContents
   ActiveWindow.ScrollRow = 3
   ActiveWindow.ScrollRow = 11
   ActiveWindow.ScrollRow = 15
   ActiveWindow.ScrollRow = 21
   ActiveWindow.ScrollRow = 25
   ActiveWindow.ScrollRow = 39
   ActiveWindow.ScrollRow = 47
   ActiveWindow.ScrollRow = 59
   ActiveWindow.ScrollRow = 67
   ActiveWindow.ScrollRow = 96
   ActiveWindow.ScrollRow = 104
   ActiveWindow.ScrollRow = 110
   ActiveWindow.ScrollRow = 116
   ActiveWindow.ScrollRow = 128
   ActiveWindow.ScrollRow = 134
   ActiveWindow.ScrollRow = 138
   ActiveWindow.ScrollRow = 144
   ActiveWindow.ScrollRow = 152
   ActiveWindow.ScrollRow = 158
   ActiveWindow.ScrollRow = 162
   ActiveWindow.ScrollRow = 164
   ActiveWindow.ScrollRow = 166
   ActiveWindow.ScrollRow = 172
   ActiveWindow.ScrollRow = 176
   ActiveWindow.ScrollRow = 178
   ActiveWindow.ScrollRow = 180
   ActiveWindow.ScrollRow = 184
   ActiveWindow.ScrollRow = 186
   ActiveWindow.ScrollRow = 188
   ActiveWindow.ScrollRow = 192
   ActiveWindow.ScrollRow = 194
   ActiveWindow.ScrollRow = 196
   ActiveWindow.ScrollRow = 200
   ActiveWindow.ScrollRow = 202
   ActiveWindow.ScrollRow = 204
   ActiveWindow.ScrollRow = 208
   ActiveWindow.ScrollRow = 210
   ActiveWindow.ScrollRow = 212
   ActiveWindow.ScrollRow = 214
   ActiveWindow.ScrollRow = 216
   ActiveWindow.ScrollRow = 220
   ActiveWindow.ScrollRow = 224
   ActiveWindow.ScrollRow = 228
   ActiveWindow.ScrollRow = 230
   ActiveWindow.ScrollRow = 232
   ActiveWindow.ScrollRow = 234
   ActiveWindow.ScrollRow = 239
   ActiveWindow.ScrollRow = 245
   ActiveWindow.ScrollRow = 259
   ActiveWindow.ScrollRow = 265
   ActiveWindow.ScrollRow = 273
   ActiveWindow.ScrollRow = 279
   ActiveWindow.ScrollRow = 285
   ActiveWindow.ScrollRow = 299
   ActiveWindow.ScrollRow = 305
   ActiveWindow.ScrollRow = 309
   ActiveWindow.ScrollRow = 313
   ActiveWindow.ScrollRow = 325
   ActiveWindow.ScrollRow = 329
   ActiveWindow.ScrollRow = 331
   ActiveWindow.ScrollRow = 337
   ActiveWindow.ScrollRow = 341
   ActiveWindow.ScrollRow = 343
   ActiveWindow.ScrollRow = 345
   ActiveWindow.ScrollRow = 347
   ActiveWindow.ScrollRow = 349
   ActiveWindow.ScrollRow = 353
   ActiveWindow.ScrollRow = 355
   ActiveWindow.ScrollRow = 357
   ActiveWindow.ScrollRow = 375
   ActiveWindow.ScrollRow = 383
   ActiveWindow.ScrollRow = 389
   ActiveWindow.ScrollRow = 396
   ActiveWindow.ScrollRow = 400
   ActiveWindow.ScrollRow = 416
   ActiveWindow.ScrollRow = 422
   ActiveWindow.ScrollRow = 428
   ActiveWindow.ScrollRow = 434
   ActiveWindow.ScrollRow = 452
   ActiveWindow.ScrollRow = 458
   ActiveWindow.ScrollRow = 462
   ActiveWindow.ScrollRow = 466
   ActiveWindow.ScrollRow = 470
   ActiveWindow.ScrollRow = 484
   ActiveWindow.ScrollRow = 488
   ActiveWindow.ScrollRow = 494
   ActiveWindow.ScrollRow = 502
   ActiveWindow.ScrollRow = 508
   ActiveWindow.ScrollRow = 516
   ActiveWindow.ScrollRow = 518
   ActiveWindow.ScrollRow = 522
   ActiveWindow.ScrollRow = 524
   ActiveWindow.ScrollRow = 528
   ActiveWindow.ScrollRow = 532
   ActiveWindow.ScrollRow = 536
   ActiveWindow.ScrollRow = 538
   ActiveWindow.ScrollRow = 536
   ActiveWindow.ScrollRow = 532
   ActiveWindow.ScrollRow = 530
   ActiveWindow.ScrollRow = 528
   ActiveWindow.ScrollRow = 526
   ActiveWindow.ScrollRow = 524
   ActiveWindow.ScrollRow = 520
   ActiveWindow.ScrollRow = 518
   ActiveWindow.ScrollRow = 516
   ActiveWindow.ScrollRow = 514
   ActiveWindow.ScrollRow = 512
   ActiveWindow.ScrollRow = 508
   ActiveWindow.ScrollRow = 504
   ActiveWindow.ScrollRow = 502
   ActiveWindow.ScrollRow = 500
   ActiveWindow.ScrollRow = 498
   ActiveWindow.ScrollRow = 494
   ActiveWindow.ScrollRow = 492
   ActiveWindow.ScrollRow = 490
   ActiveWindow.ScrollRow = 488
   ActiveWindow.ScrollRow = 486
   ActiveWindow.ScrollRow = 484
   ActiveWindow.ScrollRow = 486
   ActiveWindow.ScrollRow = 488
   ActiveWindow.ScrollRow = 490
   ActiveWindow.ScrollRow = 492
   ActiveWindow.ScrollRow = 494
   ActiveWindow.ScrollRow = 496
   ActiveWindow.ScrollRow = 498
   Range("C501:C659").Select
   Selection.Copy
   ActiveWindow.ScrollRow = 641
   ActiveWindow.ScrollRow = 635
   ActiveWindow.ScrollRow = 631
   ActiveWindow.ScrollRow = 627
   ActiveWindow.ScrollRow = 623
   ActiveWindow.ScrollRow = 619
   ActiveWindow.ScrollRow = 599
   ActiveWindow.ScrollRow = 591
   ActiveWindow.ScrollRow = 581
   ActiveWindow.ScrollRow = 569
   ActiveWindow.ScrollRow = 532
   ActiveWindow.ScrollRow = 518
   ActiveWindow.ScrollRow = 504
   ActiveWindow.ScrollRow = 490
   ActiveWindow.ScrollRow = 440
   ActiveWindow.ScrollRow = 418
   ActiveWindow.ScrollRow = 400
   ActiveWindow.ScrollRow = 383
   ActiveWindow.ScrollRow = 337
   ActiveWindow.ScrollRow = 323
   ActiveWindow.ScrollRow = 311
   ActiveWindow.ScrollRow = 307
   ActiveWindow.ScrollRow = 299
   ActiveWindow.ScrollRow = 295
   ActiveWindow.ScrollRow = 291
   ActiveWindow.ScrollRow = 289
   ActiveWindow.ScrollRow = 283
   ActiveWindow.ScrollRow = 281
   ActiveWindow.ScrollRow = 279
   ActiveWindow.ScrollRow = 275
   ActiveWindow.ScrollRow = 269
   ActiveWindow.ScrollRow = 259
   ActiveWindow.ScrollRow = 234
   ActiveWindow.ScrollRow = 226
   ActiveWindow.ScrollRow = 222
   ActiveWindow.ScrollRow = 216
   ActiveWindow.ScrollRow = 210
   ActiveWindow.ScrollRow = 206
   ActiveWindow.ScrollRow = 202
   ActiveWindow.ScrollRow = 196
   ActiveWindow.ScrollRow = 190
   ActiveWindow.ScrollRow = 180
   ActiveWindow.ScrollRow = 176
   ActiveWindow.ScrollRow = 172
   ActiveWindow.ScrollRow = 168
   ActiveWindow.ScrollRow = 164
   ActiveWindow.ScrollRow = 156
   ActiveWindow.ScrollRow = 154
   ActiveWindow.ScrollRow = 150
   ActiveWindow.ScrollRow = 148
   ActiveWindow.ScrollRow = 138
   ActiveWindow.ScrollRow = 132
   ActiveWindow.ScrollRow = 126
   ActiveWindow.ScrollRow = 122
   ActiveWindow.ScrollRow = 118
   ActiveWindow.ScrollRow = 116
   ActiveWindow.ScrollRow = 114
   ActiveWindow.ScrollRow = 112
   ActiveWindow.ScrollRow = 106
   ActiveWindow.ScrollRow = 104
   ActiveWindow.ScrollRow = 92
   ActiveWindow.ScrollRow = 88
   ActiveWindow.ScrollRow = 86
   ActiveWindow.ScrollRow = 84
   ActiveWindow.ScrollRow = 82
   ActiveWindow.ScrollRow = 80
   ActiveWindow.ScrollRow = 75
   ActiveWindow.ScrollRow = 73
   ActiveWindow.ScrollRow = 69
   ActiveWindow.ScrollRow = 65
   ActiveWindow.ScrollRow = 63
   ActiveWindow.ScrollRow = 59
   ActiveWindow.ScrollRow = 55
   ActiveWindow.ScrollRow = 51
   ActiveWindow.ScrollRow = 49
   ActiveWindow.ScrollRow = 47
   ActiveWindow.ScrollRow = 45
   ActiveWindow.ScrollRow = 43
   ActiveWindow.ScrollRow = 41
   ActiveWindow.ScrollRow = 39
   ActiveWindow.ScrollRow = 37
   ActiveWindow.ScrollRow = 35
   ActiveWindow.ScrollRow = 33
   ActiveWindow.ScrollRow = 29
   ActiveWindow.ScrollRow = 27
   ActiveWindow.ScrollRow = 25
   ActiveWindow.ScrollRow = 23
   ActiveWindow.ScrollRow = 19
   ActiveWindow.ScrollRow = 17
   ActiveWindow.ScrollRow = 15
   ActiveWindow.ScrollRow = 13
   ActiveWindow.ScrollRow = 11
   ActiveWindow.ScrollRow = 9
   ActiveWindow.ScrollRow = 7
   ActiveWindow.ScrollRow = 5
   ActiveWindow.ScrollRow = 3
   ActiveWindow.ScrollRow = 1
   Range("Q2").Select
   ActiveSheet.Paste
   Range("O1").Select
End Sub

Answer
Christopher Mitchell,

When you record a macro, it records what you do including selecting cells.  Selecting Cells (or other navigation commands) are generally not necessary to do the work.  So code like


Range("A1").Select
Selection.Copy
Range("B1").Select
Selection.Paste

can be done with   

Range("A1").copy Range("B1")

as an example.

So for your code, unless I missed something, I believe this will do what you want:

Sub core()
'
' core Macro
'

'
Range("Q2:Q501").ClearContents
Range("C501:C659").Copy Range("Q2")
Range("O1").Select
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.