How to rotate a Direction Arrow to a particular location?

Macro recognising new lines

• 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).