Results 1 to 9 of 9

Thread: Slow VBA Code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2008
    Location
    Newcastle, Australia
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Slow VBA Code

    Hi,
    I have some very basic vba code in my spreadsheet which basically finds the first blank row, inserts a new row, adds an incremental number to column A then copies the formulas down from the row above.
    It works fine, but the problem is it is very very slow. The spreadsheet is about 1200 rows, and growing.

    Any suggestions on how to speed it up?

    Thanks for your help:


    Sub NewAsset()

    'Add new line for new asset macro
    Application.ScreenUpdating = False
    Call ShowDetail

    'find next empty row
    ActiveWorkbook.Sheets("New Asset Register 11-12").Activate
    Range("A5").Select
    Do
    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True

    'copy formulas down from above row
    ActiveWorkbook.Sheets("New Asset Register 11-12").Unprotect
    ActiveCell.EntireRow.Insert
    ActiveCell.Offset(-1, 0).EntireRow.Copy
    ActiveCell.EntireRow.PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
    ActiveCell.Activate
    ActiveCell.Value = ActiveCell.Offset(-1, 0) + 1

    Application.ScreenUpdating = True
    ActiveCell.Select

    End Sub

  2. #2
    Board Regular bensonsearch's Avatar
    Join Date
    May 2011
    Location
    NSW Australia
    Posts
    844
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow VBA Code

    Hi,
    I have had a look at the code and in my opinion not really, its more the size or number of rows it has to go through that is the issue, not the code. is there a way to minimise the rows?
    Sometimes just writing down your idea and trying to get others to understand the logic makes you realise what you need to do (after all coding is nothing but logic)

    Need expert help? Try these helpful and promising people

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Slow VBA Code

    I've looked at your code and I am trying to imagine your setup. The "next empty row" that you code finds... is that the blank cell at the end of your data in Column A? If so, why are you inserting a row at that position (it's already empty I would think)? If not, then that means your blank cell is internal to the numbers in Column A... why are you adding 1 to the number in the cell above? what about the numbers in the cells of Column A that follow?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow VBA Code

    Also, what does this do?

    Code:
    Call ShowDetail

  5. #5
    New Member
    Join Date
    Sep 2008
    Location
    Newcastle, Australia
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow VBA Code

    Hi,
    Thanks for your replies. Some answers:

    So I am inserting a row at that position just to keep it within a table range that is being summed.

    No way to minimise the rows no, each row is a new asset so they all need to be separate.

    ShowDetail is another macro that is just un-hiding some columns so that those formulas get copied down as well, possibly not a necessary step.

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Slow VBA Code

    This should be faster not accounting for how long it takes to run ShowDetail

    Code:
    Sub NewAsset()
    
        'Add new line for new asset macro
        Application.ScreenUpdating = False
        Call ShowDetail
    
        With Sheets("New Asset Register 11-12")
            .Unprotect
            With .Range("A5").End(xlDown)
                .Offset(1).EntireRow.Insert
                .EntireRow.Copy
                .Offset(1).EntireRow.PasteSpecial xlPasteFormulas
                Application.CutCopyMode = False
                .Offset(1).Value = .Value + 1
            End With
            '.Protect
        End With
        Application.ScreenUpdating = True
    
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    New Member
    Join Date
    Sep 2008
    Location
    Newcastle, Australia
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow VBA Code

    Thanks AlphaFrog, works a lot faster.

    One more question, the pasteformulas code actually pastes all entered text as well as formulas. Is there a command that will paste only formulas, and leave the cells with text in them as blank?

  8. #8
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Slow VBA Code

    Code:
    Sub NewAsset()
    
        'Add new line for new asset macro
        Application.ScreenUpdating = False
        Call ShowDetail
        
        With Sheets("New Asset Register 11-12")
            .Unprotect
            With .Range("A5").End(xlDown)
                .Offset(1).EntireRow.Insert
                .EntireRow.AutoFill Destination:=.EntireRow.Resize(2)
                .Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
                .Offset(1).Value = .Value + 1
            End With
            '.Protect
        End With
        Application.ScreenUpdating = True
    
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  9. #9
    New Member
    Join Date
    Sep 2008
    Location
    Newcastle, Australia
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow VBA Code

    Beautiful. Thank you very much.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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