+ Reply to Thread
Results 1 to 7 of 7

Speeding up Macro

Hybrid View

  1. #1
    Registered User
    Join Date
    11-11-2014
    Location
    usa
    MS-Off Ver
    tn
    Posts
    23

    Speeding up Macro

    I have a Macro I recorded it takes anywhere from 18 to 20 sec to run. how can I speed it up?



    Sub TabLow()
    '
    ' TabLow Macro
    '
    
    ''PURPOSE: Determine how many seconds it took for code to completely run
    
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    'Remember time when macro starts
      StartTime = Timer
    '*****************************
    
    
    
    Application.ScreenUpdating = False
        Selection.NumberFormat = "@"
        For Each ThisCell In Selection
        If Len(ThisCell) < 10 Then
        ThisCell = Right("0000000000" & ThisCell, 10)
            Else
            End If
        Next ThisCell
        Application.ScreenUpdating = True
    
        Range("C1:C1500").Select
        Selection.Copy
        Columns("A:A").Select
        Selection.Insert Shift:=xlToRight
        Columns("A:A").EntireColumn.AutoFit
        Columns("A:C").Select
        Application.CutCopyMode = False
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        
         Application.ScreenUpdating = False
    Set rg = Selection
    For Each cel In rg.Cells
        If Len(cel.Text) = 1 Then
            cel.Value = Val(cel.Value)
            cel.NumberFormat = "00"
        End If
    Next
        
        
    
            
        '*****************************
    'Determine how many seconds code took to run
      SecondsElapsed = Round(Timer - StartTime, 2)
    
    'Notify user in seconds
      MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    
    
    End Sub

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,351

    Re: Speeding up Macro

    If you can provide an example file to work with I'm sure we can work something out.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    11-11-2014
    Location
    usa
    MS-Off Ver
    tn
    Posts
    23

    Re: Speeding up Macro

    it moves column C over to column A then if NEW column C
    is less then 2 digests it add a 0 to the front.
    Attached Files Attached Files

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Speeding up Macro

    deleted, didn't work
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  5. #5
    Registered User
    Join Date
    11-11-2014
    Location
    usa
    MS-Off Ver
    tn
    Posts
    23

    Re: Speeding up Macro

    My code works just slow i have attached an example

  6. #6
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    423

    Re: Speeding up Macro

    The reason it is so slow is that you are selecting three entire columns (i.e. 3,145,728 cells). Set rng to only the cells you need to look at, and it should improve speed massively:
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Set Rng = Range(Cells(1, 1), Cells(LastRow, 3))

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,351

    Re: Speeding up Macro

    How about 0.08 sec.

    Sub TabLow()
    '
    ' TabLow Macro
    '
    
    ''PURPOSE: Determine how many seconds it took for code to completely run
    
    Dim StartTime As Double, SecondsElapsed As Double
    Dim lRow As Long, cel As Range
    
    'Remember time when macro starts
      StartTime = Timer
    '*****************************
    With Sheets("Test File")
        lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        .Range("C1:C" & lRow).Copy
        .Range("A1:A" & lRow).Insert Shift:=xlToRight
        .Columns(1).EntireColumn.AutoFit
        Application.CutCopyMode = False
        With .Range("A1:C" & lRow)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            For Each cel In .Cells
                If Len(cel.Text) = 1 Then
                    cel.Value = Val(cel.Value)
                    cel.NumberFormat = "00"
                End If
            Next
        End With
    End With
        
        '*****************************
    'Determine how many seconds code took to run and notify user in seconds
        
      MsgBox "This code ran successfully in " & Round(Timer - StartTime, 2) & " seconds", vbInformation
        '*****************************
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help speeding up a Macro
    By jbrooks1988 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-12-2016, 12:57 PM
  2. [SOLVED] Speeding Up Macro
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-03-2015, 11:15 AM
  3. [SOLVED] Speeding up my macro
    By jsuarez199 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-03-2013, 10:52 AM
  4. [SOLVED] Help in speeding up my macro!
    By shiva_reshs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2013, 12:04 PM
  5. Speeding up a macro
    By Mister P in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-20-2010, 02:50 AM
  6. Speeding up Excel Macro
    By DanW in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2007, 01:58 PM
  7. Help with speeding up vlookup macro
    By tony.martinelli@divsys.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2006, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1