Macro recognising new lines
Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:45 PM GMT
QuestionEdit
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
AnswerEdit
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.