+ Reply to Thread
Results 1 to 3 of 3

The Sorting Macro below takes too much computing time. Help me make it more efficient,pls?

Hybrid View

  1. #1
    Registered User
    Join Date
    03-05-2011
    Location
    Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    15

    The Sorting Macro below takes too much computing time. Help me make it more efficient,pls?

    Hey, you all!
    I wrote this macro myself, so I can only assume it is full of inefficiencies, but can you help me improve it?

    It simply aligns an entire table to the right by copy pasting the values of the cells. Unfortunately the looping through the "if" statement makes it super slow, which looks to me like a rather easy task (check if empty, if not -> copy-paste, if yes ->next)
    What am I missing here? Can anyone think of a faster way to do this? Funnily enough, I have been using this macro for a few months and I can swear that it used to be faster at the beginning! Either my computer has gone slower or I am getting nuts, neither option particularly exciting.



    a b c 1 2                                         a b c 1 2
    k l m 3 4 5 6 7                             k l m 3 4 5 6 7   
    x y z 8                  becomes                    x y z 8


    Sub Allign_Right()
    
    Dim StartColumn As Integer
    Dim EndColumn As Integer
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Row  As Integer
    Dim Column As Integer
    Dim MaxColumn As Integer
    Dim MaxColumnVar As Integer
    Dim MaxColumnCount As Integer
        
    StartColumn = 2
    EndColumn = 30
    StartRow = 1
    EndRow = 30
        
    For Row = StartRow To EndRow
        With ActiveSheet
            MaxColumnVar = .Cells(Row, .Columns.Count).End(xlToLeft).Column
        End With
        If MaxColumnVar > MaxColumn Then
        MaxColumn = MaxColumnVar
        End If
    Next Row
    
    
    For Row = StartRow To EndRow
        MaxColumnCount = 0                              ' Should be always 0 at start.
    For Column = MaxColumn To StartColumn Step -1
        Cells(Row, Column).Activate
        If ActiveCell.Value <> "" Then
        Cells(Row, MaxColumn + 1 - MaxColumnCount).Value = ActiveCell.Value
        Cells(Row, Column).Clear
        MaxColumnCount = MaxColumnCount + 1
        Else
        End If
    Next Column
    Next Row
    
    Cells(1, 1).Activate
    End Sub

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: The Sorting Macro below takes too much computing time. Help me make it more efficient,

    maybe something like this
    Sub tt()
    Dim r As Range
    On Error Resume Next
    For Each r In Range("A1").CurrentRegion.Rows
        r.Cells(1, 1).Resize(, r.SpecialCells(4).Count).Insert Shift:=xlToRight
    Next
    End Sub

  3. #3
    Registered User
    Join Date
    03-05-2011
    Location
    Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: The Sorting Macro below takes too much computing time. Help me make it more efficient,

    It works great and it's obviously a much smarter way to do it.
    Can you give me a 3-sentence explanation what is happening there, please? Would be awesome, because I am far away from being a pro and I don't understand how the Macro chooses the CurrentRegion and then resizes the SpecialCells? Also what is the error event that stops the loop?
    I don't want to waste your time, just a quick explanation would be great, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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