Macro recognising new lines

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:45 PM GMT

Question

QUESTION: Question 1: I have recorded a macro that could be used by my team but the data in the worksheet download will grow every month and I am stuck as to how to make the macro continue to read all data including the new ones for the subsequent periods/months. Question 2: how do I share the macro with my colleagues. I have saved my macro and an Add-In but do not know what my colleagues have to do to access the macro.

Grateful for you help.

Many Enoch

ANSWER: Enoch Osaji-Okai,

If you wanted to process data in column A for as far as there is data, instead of using

Range("A1:A200").Select Selection.copy

you would use

Range("A1",cells(rows.count,"A").End(xlup)).Select Selection.Copy


this is the same as going to the bottom of column A (in xl2003 and earlier, to A65536 and in Excel 2007 down to the millionth row), hitting end key and then the up arrow key

You will see that the cursor jumps up to the last filled cell. (That is what cells(rows.count,"A").End(xlup) is simulating

---

You would send your addin to each person or put it in a common location. They would then load the addin and once loaded, they would see your macro in tools=>macro=>macros. The would be able to select it and run it like any macro.

in Excel 2007, it would be in the macro dropdown on the right end of the View Tab on the Ribbon.

You load addins in xl2003 and earlier in tools=>Addins

in xl2007, it is done in the options selected in the lower right border in the dialog you get when you click the office button.


Jan Karl Pieterse has a nice writeup on how to distribute code using addins.

http://www.jkp-ads.com/Articles/DistributeMacro00.htm

that might give you some insights.



---------- FOLLOW-UP ----------

QUESTION: I am not sure how or where to add your code. I have not edited the macro since I recorded it so I thought may be if I copied and pasted it here you will be able to point me to the right direction. I am sorry to trouble you further:

Sub ExpandedBMR() ' ' ExpandedBMR Macro ' Macro recorded 13/08/2010 by osaji-e ' ' Keyboard Shortcut: Ctrl+Shift+E '

   Columns("B:B").Select
   Range("B3").Activate
   Selection.Insert Shift:=xlToRight
   Selection.Insert Shift:=xlToRight
   Selection.Insert Shift:=xlToRight
   Range("B23").Select
   ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],13)"
   Range("B23").Select
   Columns("B:B").EntireColumn.AutoFit
   Range("C23").Select
   ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],6)"
   Range("C23").Select
   ActiveWindow.SmallScroll Down:=-3
   ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],6)"
   Range("D23").Select
   Columns("C:C").EntireColumn.AutoFit
   ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],6)"
   Range("A13").Select
   ActiveCell.FormulaR1C1 = "Combintioations"
   Range("B13").Select
   ActiveCell.FormulaR1C1 = "CC&ACC"
   Range("C13").Select
   ActiveCell.FormulaR1C1 = "CC"
   Range("D13").Select
   ActiveCell.FormulaR1C1 = "ACC"
   Range("E13").Select
   Columns("D:D").EntireColumn.AutoFit
   Columns("E:E").Select
   Range("E3").Activate
   Selection.Delete Shift:=xlToLeft
   ActiveWindow.ScrollColumn = 2
   Range("B23:D23").Select
   Selection.AutoFill Destination:=Range("B23:D983"), Type:=xlFillDefault
   Range("B23:D983").Select
   Range("B14").Select
   ActiveWindow.SmallScroll Down:=12
   Range("B38:D40").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=0
   Range("B40:D41").Select
   Range("B41").Activate
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=9
   Range("B49:D49").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=36
   Range("B83:D83").Select
   Selection.ClearContents
   Range("B86:D90").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=9
   Range("B93:D95").Select
   Selection.ClearContents
   Range("B96:D96").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=117
   Range("B209:D215").Select
   Selection.ClearContents
   Range("B216:D216").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B219:D220").Select
   Selection.ClearContents
   Range("B225:D227").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B238:D240").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=33
   Range("B264:D264").Select
   Selection.ClearContents
   Range("B268:D268").Select
   Selection.ClearContents
   Range("B271:D271").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=6
   Range("B279:D281").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B286:D288").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B302:D303").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=9
   Range("B309:D309").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=9
   Range("B315:D316").Select
   Selection.ClearContents
   Range("B319:D321").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B327:D331").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=6
   Range("B334:D355").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=45
   Range("B400:D403").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=75
   Range("B475:D475").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=150
   Range("B621:D622").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=15
   Range("B633:D633").Select
   Selection.ClearContents
   Range("B635:D635").Select
   Selection.ClearContents
   Range("B637").Select
   Selection.ClearContents
   Selection.ClearContents
   Range("B639:B640").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B647:B649").Select
   Selection.ClearContents
   Range("B650").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=0
   Range("C650:D650").Select
   Selection.ClearContents
   Range("C647:D647").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=-12
   Range("C637:D640").Select
   Selection.ClearContents
   Range("D633").Select
   ActiveWindow.SmallScroll Down:=63
   Range("B701:D708").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B713:D713").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=30
   Range("B740:D741").Select
   Selection.ClearContents
   Selection.ClearContents
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=6
   Range("B745:D746").Select
   Selection.ClearContents
   Range("B752:D752").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=18
   Range("B763:D763").Select
   Selection.ClearContents
   Range("B761:D761").Select
   Selection.ClearContents
   Range("B767:D768").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B771:D772").Select
   Selection.ClearContents
   Range("B777:D780").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=9
   Range("B787:D789").Select
   Selection.ClearContents
   Range("B790:D790").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=6
   Range("B793:D794").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=24
   Range("B819:D819").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=12
   Range("B831:D831").Select
   Selection.ClearContents
   Range("B834:D835").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=39
   Range("B865:E875").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=15
   Range("B879:D893").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=15
   Range("B897:D910").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=15
   Range("B915:D915").Select
   Selection.ClearContents
   Range("B922:D922").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=18
   Range("B924:D924").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=30
   Range("B958:D964").Select
   Selection.ClearContents
   Range("B966:D972").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=15
   Range("B976:D977").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=-3
   Range("B978:D983").Select
   Selection.ClearContents
   ActiveWindow.SmallScroll Down:=-27
   Range("E11:N11").Select
   Selection.Copy
   ActiveWindow.LargeScroll ToRight:=-1
   Range("E13").Select
   ActiveSheet.Paste
   Range("A13").Select
   Range(Selection, Selection.End(xlToRight)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   ActiveWindow.SmallScroll Down:=18
   Range("A13:N983").Select
   ActiveWindow.SmallScroll Down:=-24
   Application.CutCopyMode = False
   Selection.AutoFilter
   Selection.AutoFilter Field:=14, Criteria1:="0.00"
   ActiveWindow.SmallScroll Down:=-66
   Selection.AutoFilter Field:=14
   ActiveWindow.SmallScroll Down:=-51
   Range("O13").Select
   Selection.Style = "Output Column Headings"
   Selection.Borders(xlDiagonalDown).LineStyle = xlNone
   Selection.Borders(xlDiagonalUp).LineStyle = xlNone
   Selection.Borders(xlEdgeLeft).LineStyle = xlNone
   Selection.Borders(xlEdgeTop).LineStyle = xlNone
   Selection.Borders(xlEdgeBottom).LineStyle = xlNone
   Selection.Borders(xlEdgeRight).LineStyle = xlNone
   Selection.Borders(xlInsideVertical).LineStyle = xlNone
   Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   ActiveCell.FormulaR1C1 = "Total"
   Range("O23").Select
   ActiveCell.FormulaR1C1 = "=SUM(RC[-10]:RC[-1])"
   Range("O23").Select
   Selection.AutoFill Destination:=Range("O23:O38")
   Range("O23:O38").Select
   Selection.End(xlDown).Select
   ActiveWindow.SmallScroll Down:=15
   Range("O38").Select
   Selection.AutoFill Destination:=Range("O38:O983"), Type:=xlFillDefault
   Range("O38:O983").Select
   Range("O13").Select
   Range(Selection, Selection.End(xlToLeft)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlUp)).Select
   Selection.AutoFilter
   ActiveWindow.ScrollRow = 981
   ActiveWindow.ScrollRow = 978
   ActiveWindow.ScrollRow = 976
   ActiveWindow.ScrollRow = 973
   ActiveWindow.ScrollRow = 971
   ActiveWindow.ScrollRow = 967
   ActiveWindow.ScrollRow = 963
   ActiveWindow.ScrollRow = 960
   ActiveWindow.ScrollRow = 957
   ActiveWindow.ScrollRow = 948
   ActiveWindow.ScrollRow = 942
   ActiveWindow.ScrollRow = 932
   ActiveWindow.ScrollRow = 920
   ActiveWindow.ScrollRow = 909
   ActiveWindow.ScrollRow = 896
   ActiveWindow.ScrollRow = 886
   ActiveWindow.ScrollRow = 873
   ActiveWindow.ScrollRow = 863
   ActiveWindow.ScrollRow = 851
   ActiveWindow.ScrollRow = 845
   ActiveWindow.ScrollRow = 833
   ActiveWindow.ScrollRow = 826
   ActiveWindow.ScrollRow = 817
   ActiveWindow.ScrollRow = 812
   ActiveWindow.ScrollRow = 805
   ActiveWindow.ScrollRow = 797
   ActiveWindow.ScrollRow = 789
   ActiveWindow.ScrollRow = 782
   ActiveWindow.ScrollRow = 772
   ActiveWindow.ScrollRow = 765
   ActiveWindow.ScrollRow = 761
   ActiveWindow.ScrollRow = 756
   ActiveWindow.ScrollRow = 751
   ActiveWindow.ScrollRow = 744
   ActiveWindow.ScrollRow = 739
   ActiveWindow.ScrollRow = 734
   ActiveWindow.ScrollRow = 729
   ActiveWindow.ScrollRow = 724
   ActiveWindow.ScrollRow = 723
   ActiveWindow.ScrollRow = 718
   ActiveWindow.ScrollRow = 714
   ActiveWindow.ScrollRow = 711
   ActiveWindow.ScrollRow = 709
   ActiveWindow.ScrollRow = 704
   ActiveWindow.ScrollRow = 703
   ActiveWindow.ScrollRow = 701
   ActiveWindow.ScrollRow = 700
   ActiveWindow.ScrollRow = 698
   ActiveWindow.ScrollRow = 696
   ActiveWindow.ScrollRow = 695
   ActiveWindow.ScrollRow = 693
   ActiveWindow.ScrollRow = 691
   ActiveWindow.ScrollRow = 690
   ActiveWindow.ScrollRow = 688
   ActiveWindow.ScrollRow = 685
   ActiveWindow.ScrollRow = 683
   ActiveWindow.ScrollRow = 681
   ActiveWindow.ScrollRow = 680
   ActiveWindow.ScrollRow = 678
   ActiveWindow.ScrollRow = 676
   ActiveWindow.ScrollRow = 675
   ActiveWindow.ScrollRow = 673
   ActiveWindow.ScrollRow = 672
   ActiveWindow.ScrollRow = 670
   ActiveWindow.ScrollRow = 668
   ActiveWindow.ScrollRow = 667
   ActiveWindow.ScrollRow = 665
   ActiveWindow.ScrollRow = 663
   ActiveWindow.ScrollRow = 662
   ActiveWindow.ScrollRow = 660
   ActiveWindow.ScrollRow = 658
   ActiveWindow.ScrollRow = 657
   ActiveWindow.ScrollRow = 653
   ActiveWindow.ScrollRow = 650
   ActiveWindow.ScrollRow = 647
   ActiveWindow.ScrollRow = 645
   ActiveWindow.ScrollRow = 640
   ActiveWindow.ScrollRow = 637
   ActiveWindow.ScrollRow = 634
   ActiveWindow.ScrollRow = 630
   ActiveWindow.ScrollRow = 629
   ActiveWindow.ScrollRow = 627
   ActiveWindow.ScrollRow = 625
   ActiveWindow.ScrollRow = 624
   ActiveWindow.ScrollRow = 622
   ActiveWindow.ScrollRow = 620
   ActiveWindow.ScrollRow = 619
   ActiveWindow.ScrollRow = 617
   ActiveWindow.ScrollRow = 614
   ActiveWindow.ScrollRow = 612
   ActiveWindow.ScrollRow = 611
   ActiveWindow.ScrollRow = 609
   ActiveWindow.ScrollRow = 606
   ActiveWindow.ScrollRow = 604
   ActiveWindow.ScrollRow = 601
   ActiveWindow.ScrollRow = 599
   ActiveWindow.ScrollRow = 597
   ActiveWindow.ScrollRow = 592
   ActiveWindow.ScrollRow = 587
   ActiveWindow.ScrollRow = 583
   ActiveWindow.ScrollRow = 578
   ActiveWindow.ScrollRow = 574
   ActiveWindow.ScrollRow = 568
   ActiveWindow.ScrollRow = 564
   ActiveWindow.ScrollRow = 558
   ActiveWindow.ScrollRow = 555
   ActiveWindow.ScrollRow = 546
   ActiveWindow.ScrollRow = 543
   ActiveWindow.ScrollRow = 540
   ActiveWindow.ScrollRow = 536
   ActiveWindow.ScrollRow = 531
   ActiveWindow.ScrollRow = 528
   ActiveWindow.ScrollRow = 527
   ActiveWindow.ScrollRow = 523
   ActiveWindow.ScrollRow = 520
   ActiveWindow.ScrollRow = 517
   ActiveWindow.ScrollRow = 513
   ActiveWindow.ScrollRow = 508
   ActiveWindow.ScrollRow = 505
   ActiveWindow.ScrollRow = 500
   ActiveWindow.ScrollRow = 499
   ActiveWindow.ScrollRow = 492
   ActiveWindow.ScrollRow = 487
   ActiveWindow.ScrollRow = 480
   ActiveWindow.ScrollRow = 477
   ActiveWindow.ScrollRow = 470
   ActiveWindow.ScrollRow = 466
   ActiveWindow.ScrollRow = 459
   ActiveWindow.ScrollRow = 456
   ActiveWindow.ScrollRow = 446
   ActiveWindow.ScrollRow = 441
   ActiveWindow.ScrollRow = 438
   ActiveWindow.ScrollRow = 429
   ActiveWindow.ScrollRow = 426
   ActiveWindow.ScrollRow = 419
   ActiveWindow.ScrollRow = 414
   ActiveWindow.ScrollRow = 408
   ActiveWindow.ScrollRow = 405
   ActiveWindow.ScrollRow = 400
   ActiveWindow.ScrollRow = 395
   ActiveWindow.ScrollRow = 388
   ActiveWindow.ScrollRow = 383
   ActiveWindow.ScrollRow = 378
   ActiveWindow.ScrollRow = 373
   ActiveWindow.ScrollRow = 367
   ActiveWindow.ScrollRow = 362
   ActiveWindow.ScrollRow = 355
   ActiveWindow.ScrollRow = 352
   ActiveWindow.ScrollRow = 349
   ActiveWindow.ScrollRow = 344
   ActiveWindow.ScrollRow = 340
   ActiveWindow.ScrollRow = 335
   ActiveWindow.ScrollRow = 332
   ActiveWindow.ScrollRow = 325
   ActiveWindow.ScrollRow = 321
   ActiveWindow.ScrollRow = 316
   ActiveWindow.ScrollRow = 311
   ActiveWindow.ScrollRow = 301
   ActiveWindow.ScrollRow = 296
   ActiveWindow.ScrollRow = 288
   ActiveWindow.ScrollRow = 283
   ActiveWindow.ScrollRow = 274
   ActiveWindow.ScrollRow = 269
   ActiveWindow.ScrollRow = 260
   ActiveWindow.ScrollRow = 255
   ActiveWindow.ScrollRow = 246
   ActiveWindow.ScrollRow = 240
   ActiveWindow.ScrollRow = 235
   ActiveWindow.ScrollRow = 227
   ActiveWindow.ScrollRow = 220
   ActiveWindow.ScrollRow = 212
   ActiveWindow.ScrollRow = 205
   ActiveWindow.ScrollRow = 197
   ActiveWindow.ScrollRow = 190
   ActiveWindow.ScrollRow = 182
   ActiveWindow.ScrollRow = 177
   ActiveWindow.ScrollRow = 171
   ActiveWindow.ScrollRow = 166
   ActiveWindow.ScrollRow = 159
   ActiveWindow.ScrollRow = 154
   ActiveWindow.ScrollRow = 147
   ActiveWindow.ScrollRow = 144
   ActiveWindow.ScrollRow = 138
   ActiveWindow.ScrollRow = 133
   ActiveWindow.ScrollRow = 129
   ActiveWindow.ScrollRow = 124
   ActiveWindow.ScrollRow = 123
   ActiveWindow.ScrollRow = 119
   ActiveWindow.ScrollRow = 115
   ActiveWindow.ScrollRow = 110
   ActiveWindow.ScrollRow = 108
   ActiveWindow.ScrollRow = 105
   ActiveWindow.ScrollRow = 103
   ActiveWindow.ScrollRow = 96
   ActiveWindow.ScrollRow = 95
   ActiveWindow.ScrollRow = 90
   ActiveWindow.ScrollRow = 88
   ActiveWindow.ScrollRow = 83
   ActiveWindow.ScrollRow = 80
   ActiveWindow.ScrollRow = 75
   ActiveWindow.ScrollRow = 70
   ActiveWindow.ScrollRow = 65
   ActiveWindow.ScrollRow = 60
   ActiveWindow.ScrollRow = 55
   ActiveWindow.ScrollRow = 49
   ActiveWindow.ScrollRow = 45
   ActiveWindow.ScrollRow = 39
   ActiveWindow.ScrollRow = 34
   ActiveWindow.ScrollRow = 27
   ActiveWindow.ScrollRow = 24
   ActiveWindow.ScrollRow = 17
   ActiveWindow.ScrollRow = 14
   ActiveWindow.ScrollRow = 983
   Selection.AutoFilter
   Selection.AutoFilter Field:=15, Criteria1:="0.00"
   ActiveWindow.ScrollColumn = 7
   ActiveWindow.ScrollColumn = 6
   ActiveWindow.ScrollColumn = 5
   ActiveWindow.ScrollColumn = 4
   ActiveWindow.ScrollColumn = 3
   ActiveWindow.ScrollColumn = 2
   Range("F13").Select
   Selection.AutoFilter Field:=8, Criteria1:="0.00"
   Selection.AutoFilter Field:=8
   Range("K13").Select
   ActiveWindow.SmallScroll ToRight:=6
   Range("B39").Select
   ActiveWindow.SmallScroll Down:=-15
   ActiveWindow.SmallScroll ToRight:=10
   ActiveWindow.SmallScroll Down:=-27
   Rows("39:215").Select
   ActiveWindow.ScrollColumn = 11
   ActiveWindow.ScrollColumn = 10
   ActiveWindow.ScrollColumn = 9
   ActiveWindow.ScrollColumn = 8
   ActiveWindow.ScrollColumn = 7
   ActiveWindow.ScrollColumn = 6
   ActiveWindow.ScrollColumn = 5
   ActiveWindow.ScrollColumn = 4
   ActiveWindow.ScrollColumn = 3
   ActiveWindow.ScrollColumn = 2
   ActiveWindow.ScrollColumn = 12
   ActiveWindow.LargeScroll ToRight:=-1
   Range("A216").Select
   ActiveWindow.SmallScroll Down:=-9
   Rows("39:713").Select
   Selection.Delete Shift:=xlUp
   ActiveWindow.SmallScroll Down:=-63
   Rows("642:991").Select
   Selection.Delete Shift:=xlUp
   ActiveWindow.SmallScroll Down:=3
   ActiveWindow.ScrollColumn = 3
   ActiveWindow.ScrollColumn = 4
   ActiveWindow.ScrollColumn = 5
   ActiveWindow.ScrollColumn = 6
   ActiveWindow.LargeScroll ToRight:=1
   Selection.AutoFilter Field:=15
   Range("B23:D23").Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlUp)).Select
   ActiveWindow.SmallScroll Down:=-6
   Selection.Copy
   ActiveSheet.Paste
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Range("B23").Select
   Columns("O:O").Select
   Range("O3").Activate
   Application.CutCopyMode = False
   Selection.Delete Shift:=xlToLeft
   Range("B14").Select

End Sub


Kind Enoch

Answer

Enoch,

I had assumed you were selecting a large area of data and doing some steps like applying formatting.

In fact, you have recorded yourself inserting some columns, putting in some formulas and copy them down the sheet, then erasing data from various rows using code such as this:

  Range("B40:D41").Select
  Range("B41").Activate
  Selection.ClearContents
  ActiveWindow.SmallScroll Down:=9
  Range("B49:D49").Select
  Selection.ClearContents
  ActiveWindow.SmallScroll Down:=36
  Range("B83:D83").Select
  Selection.ClearContents
  Range("B86:D90").Select
  Selection.ClearContents
  ActiveWindow.SmallScroll Down:=9
  Range("B93:D95").Select
  Selection.ClearContents
  Range("B96:D96").Select
  Selection.ClearContents

But there is no pattern to that action - no rule you show in your code that could be applied to making those cell clearances. You code continues with those types of operations where one would have to have the sheet in front of them to see what it is actually doing and have to know what you are doing to understand.

That type of code can not be expanded without that knowledge - How would know why you scrolled down 9 or scrolled down 36 and so forth - just as an example. (in fact all that scrolling code can be thrown out). If you had rules and your procedure was well defined, it would probably take 10 or 20 lines of code to do what you are trying to do (just a wild guess since it isn't clear what your rules are or what you are trying to do).

You continue on with more actions - but a lot of them are for specific locations as well.

here is a specific example of where my suggestion might work (but it is only for a small part of your code).

At one point, you have this line

Range(Selection, Selection.End(xlDown)).Select

repeated around 39 times, but at the end you do 

Range("A13:N983").Select

which would negate all those other commands.  

My code could make that last command dynamic (you don't need the 39 repeats of the above line at all)

Range("A13:N983").Select woud be Range("A13",cells(rows.count,1).End(xlup)).Resize(,14).select

would select A13:N983 if the last data in column A was in cell A983 as an example.

then you could immediately do

Selection.AutoFilter Field:=14, Criteria1:="0.00"

so Range("A13",cells(rows.count,1).End(xlup)).Resize(,14).select Selection.AutoFilter Field:=14, Criteria1:="0.00"

but I am afraid that would not be compatible with all the hard coded things you have in your macro.

so your recorded code in its current form is not set up to allow expansion. Sorry - but that is a fact. the problem with recorded code is that it records your movements as well as the meaningful actions. All the scrollrow code is your movements - it is not needed in the code (that is another example).

Advertisement

©2024 eLuminary LLC. All rights reserved.