excel vba copy row

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA Copy Row Intermittent Crash

Excel VBA Copy Row Intermittent Crash

resolvedResolved · Medium Priority · Version 2013

Annabel has attended:
Excel VBA Advanced course

Excel VBA Copy Row Intermittent Crash

I have a push button in a merged cell which copies all the rows of the merged cells and inserts them below those rows. The code works fine for the majority of the time. However, on some occasions the code causes Excel to close without warning. With some debugging I have identified the row it does this on is the insert row line. Do you know what might cause this crash? My code (with irrelevant parts removed) is below:

Sub CopyRows()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.CutCopyMode = True

Dim Top As Integer
Dim Bottom As Integer
Dim NumRows As Integer

ActiveSheet.Buttons(Application.Caller).TopLeftCell.Select

NumRows = ActiveCell.MergeArea.Rows.Count
Top = ActiveCell.Row
Bottom = NumRows + Top - 1

ActiveSheet.Rows(Top & ":" & Bottom).EntireRow.Copy
ActiveSheet.Range("A" & Bottom + 1).EntireRow.Insert shift:=xlDown '<<<< this is where it crashes
Application.CutCopyMode = False
Sleep 100

Top = Bottom + 1
Bottom = NumRows + Top - 1
ActiveSheet.Range("D" & Top & ":D" & Bottom).ClearContents
Sleep 100

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

RE: Excel VBA Copy Row Intermittent Crash

Hi Annabel,

Thank you for the forum question.

I can not see anything in the code which should cause the crash. When Excel crash unexpected is normally when the memory is full. Copy and Paste is something I try to avoid doing in VBA. I have had a lot of problems Copy and Paste.

Can an Array not do the job???

Arrays run faster and do not push the memory as much as Copy & Paste.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA Copy Row Intermittent Crash

Hi Jens

Thank you for coming back to me. Unfortunately arrays will not work in this case as I need to copy data validations and conditional formatting.

Is there a way to clear additional memory space in the code or check there is enough memory before performing the paste to avoid the crashing?

Many thanks again,

Annabel

RE: Excel VBA Copy Row Intermittent Crash

Hi Annabel,

To clean memory before running the code. Well close down other applications and clear variables not in use.

If you are using object variable you can set the object to nothing. This will clear it from the memory.

Set Bk=nothing

And data variables:

strString=""

iNumber=0

instead of copying the entire row you can copy only columns with content. This will use less space in your memory.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Create a unique items table from a duplicating table

1. Ensure that your list has column headings
2. Select the entire list
3. From the menu bar, select DATA, FILTER, ADVANCED FILTER
4. Select "Filter the list, in place", and tick the "Unique Records Only" box
5. Click OK, filtered list appears.

View all Excel hints and tips


Server loaded in 0.06 secs.