98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA Copy Row Intermittent Crash
Excel VBA Copy Row Intermittent Crash
Resolved · 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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Create a unique items table from a duplicating table1. Ensure that your list has column headings |