Slow VBA Code

libk444

New Member
Joined
Sep 3, 2008
Messages
25
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top