Thread: Macros are VERY slow to run
View Single Post
 
Old 09-07-2014, 06:47 AM
wxtoad wxtoad is offline Windows 7 64bit Office 2013
Novice
  
Join Date: Sep 2014
Posts: 6
wxtoad is on a distinguished road
Default
This was done by recording the steps. In executing the macro, the screen often goes white, a message saying Excel not responding repeatedly appears, and it took about 10 minutes to complete.

This same macro worked fine with my former Excel 2003, but 2013 is a problem.

As to what I'm trying to accomplish with this particular process is to add 1 or 2 leading zeros to a series of numbers in a string; i.e., convert this:

3,5,6,8,9,12,14

to this:

003,005,006,008,009,012,014

There is undoubtedly an easier way, and I guess I need to buckle down and learn VBA so I can write the macro.

The over-arching issue, though, is limiting the function to just the populated rows and not the entire sheet.

Thanks for your assistance.


Code:
Sub Activities()
'
' Activities Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Columns("T:T").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    Columns("AN:AN").Select
    ActiveSheet.Paste
    Columns("AN:BP").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "General"
    Range("AN1").Select
    Selection.ClearContents
    Columns("AN:AN").Select
    Selection.TextToColumns Destination:=Range("AN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 48
    Range("AX1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-10]="""","""",IF(LEN(RC[-10])=2,CONCATENATE(""0"",RC[-10]),CONCATENATE(""00"",RC[-10])))"
    Columns("AX:AX").Select
    Selection.FillDown
    Range("AX1").Select
    Selection.Copy
    Range("AY1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("AY:AY").Select
    Selection.FillDown
    Range("AY1").Select
    Selection.Copy
    Range("AZ1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("AZ:AZ").Select
    Selection.FillDown
    Range("AZ1").Select
    Selection.Copy
    Columns("BA:BA").Select
    Application.CutCopyMode = False
    Selection.FillDown
    Range("AZ1").Select
    Selection.Copy
    Range("BA1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BA:BA").Select
    Selection.FillDown
    Range("BA1").Select
    Selection.Copy
    Range("BB1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BB:BB").Select
    Selection.FillDown
    Range("BB1").Select
    Selection.Copy
    Range("BC1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BC:BC").Select
    Selection.FillDown
    Range("BC1").Select
    Selection.Copy
    Range("BD1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BD:BD").Select
    Selection.FillDown
    Range("BD1").Select
    Selection.Copy
    Range("BE1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BE:BE").Select
    Selection.FillDown
    Range("BE1").Select
    Selection.Copy
    Range("BF1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BF:BF").Select
    Selection.FillDown
    Range("BF1").Select
    Selection.Copy
    Range("BG1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BG:BG").Select
    Selection.FillDown
    Range("BI1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-11]="""","""",CONCATENATE(RC[-11],"",""))"
    Columns("BI:BI").Select
    Selection.FillDown
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 54
    Range("BI1").Select
    Selection.Copy
    Range("BJ1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BJ:BJ").Select
    Selection.FillDown
    Range("BJ1").Select
    Selection.Copy
    Range("BK1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BK:BK").Select
    Selection.FillDown
    Range("BK1").Select
    Selection.Copy
    Range("BL1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BL:BL").Select
    Selection.FillDown
    Range("BL1").Select
    Selection.Copy
    Range("BM1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BM:BM").Select
    Selection.FillDown
    Range("BM1").Select
    Selection.Copy
    Range("BN1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BN:BN").Select
    Selection.FillDown
    Range("BN1").Select
    Selection.Copy
    Range("BO1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BO:BO").Select
    Selection.FillDown
    Range("BO1").Select
    Selection.Copy
    Range("BP1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BP:BP").Select
    Selection.FillDown
    ActiveWindow.SmallScroll ToRight:=4
    Range("BP1").Select
    Selection.Copy
    Range("BQ1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BQ:BQ").Select
    Selection.FillDown
    Range("BQ1").Select
    Selection.Copy
    Range("BR1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("BR:BR").Select
    Selection.FillDown
    Range("BS1").Select
    ActiveWindow.ScrollColumn = 57
    ActiveWindow.ScrollColumn = 56
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    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 = 1
    Columns("E:E").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.SmallScroll Down:=-68
    ActiveWindow.SmallScroll ToRight:=4
    ActiveWindow.SmallScroll Down:=-34
    Range("BS1").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
    Columns("BS:BS").Select
    Selection.FillDown
    Range("BT1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RIGHT(RC[-1],1)="","",LEFT(RC[-1],LEN(RC[-1])-1),RC[-1])"
    Columns("BT:BT").Select
    Selection.FillDown
    Columns("BT:BT").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=-3
    Columns("T:T").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("T1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "activities"
    Columns("E:E").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    Range("A1").Select
End Sub
Reply With Quote