Results 1 to 8 of 8

Help with code efficiency - routine takes WAY too long to run

Threaded View

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    NC
    MS-Off Ver
    2010
    Posts
    10

    Help with code efficiency - routine takes WAY too long to run

    I'm a VBA novice. I have a spreadsheet with 5 columns of data in columns A through E with any number of rows (usually thousands). The problem is that for some of the rows the data is shifted to the right one column (in columns B through F). I wrote a macro to shift the rows that were off; the macro evaluates each row and if column A is empty, it cuts the 5 cells to the right and pastes them and then moves to the next row, if not empty it just moves to next row. What I came up with works, but takes way too long and heats up my laptop. I've searched ways to make the code more efficient, but have not come up with something that does the trick.

    I'm requesting two things. First, given the code I have (the process of checking each row one by one), is there something I can do to make it run faster? Even if there is a better way to approach this, I'd love to learn how to make something like this faster if that is possible.

    Second, I know there has to be a better way to approach this. Any suggestions will help me learn how to do things better.

    Thanks in advance.
    Doug

    Sub Shift()
    
    Application.ScreenUpdating = False
    Dim RowCount As Integer
    Dim lRow As Long
        
    'Finds the last non-blank cell on a sheet/range.
        lRow = Cells.Find(What:="*", _
                        After:=Range("A1"), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
    
    ' move cursor to cell A1    
        Range("A1").Select
        ActiveCell.Offset(1, 0).Select
    
    For RowCount = 2 To lRow
    
        If ActiveCell.Value = "" Then
           ActiveCell.Offset(, 1).Resize(1, 5).Cut
           ActiveSheet.Paste
           ActiveCell.Offset(1, 0).Select
        Else
           ActiveCell.Offset(1, 0).Select
        End If
    
    Next RowCount
    
    Application.ScreenUpdating = True
    End Sub
    Last edited by robertsfd2002; 06-24-2018 at 12:33 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] This code takes way to long to run
    By Jym396 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2017, 11:07 AM
  2. Inefficient code - macro takes too long
    By dantray02 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-07-2014, 09:29 AM
  3. [SOLVED] Long VBA Code - Improve Efficiency?
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-18-2013, 11:29 AM
  4. efficiency recomendations, code taking long time to execute...
    By am_hawk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2013, 03:55 PM
  5. [SOLVED] To improve Efficiency of code, code running too long
    By andywsw in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2012, 05:54 PM
  6. Long code takes an age to run
    By E3iron in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-05-2009, 07:38 AM
  7. My Code takes too long to execute
    By coreytroy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-30-2008, 12:12 AM

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