Temporary "hanging" when running code

FredMFoley

Board Regular
Joined
Mar 18, 2002
Messages
58
When I run my code, it copies a large selection of cells (about 220 columns by 2200 rows) in order to move them one column to the right.
It then enters new data into column 30.

Range(Cells(1, 30), Cells(ActiveSheet.UsedRange.Rows.Count, 252)).Select
Selection.Copy
Cells(1,30).Select
ActiveSheet.Paste

I run into this problem when I have more than one set of data to enter.

When I open the workbook and run the code it runs normally. It takes about 1min 23 sec to complete.

However if I re-run it (exactly the same code) to get the next set of data, without closing and re-opening the workbook, then the VBA "hangs" for about 70 seconds on the line Selection.Copy
It takes about 2min 35sec to complete the second (and subsequent) sets of data.
The code ruins at normal speed before and after that line.

During the time it is "hanging", the Windows Task Manager shows Excel is "not responding". (Before and after the "copying" it says it is running.)

Something is snagging on that Selection.Copy line which won't clear unless the workbook is closed and re-opened. (I know it hangs exactly on that line by stepping through the code)

Someting gets cleared or reset by re-opening the workbook.

Simply saving the workbook between sets of data doesn't "fix" it.

Any help or suggestions would be very welcome.

Thank You
Fred

Other information:
Windows XP Pro
Microsoft Excel 2003
workbook is large (15MB) with a large VBA program
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Are you moving all your data starting at AD1 one column to the right?
If so, why not just insert a column?

Also, you don't need to select a range to work with it.
Why are you pasting the data into the same location it started in?
 
Upvote 0
Thanks for the reply.

I made a mess of the piece of code I put up.
I'm pasting into Col 31, not 30
I was just trying to make it really clear that it is the copy action which "hangs" the running of the code for 70 seconds.

This was my original code

Range(Cells(1, 30), Cells(ActiveSheet.UsedRange.Rows.Count, 252)).Copy
Range("AE1").Select
ActiveSheet.Paste

Inserting a column does not work on my worksheet. The message that Excel can't shift data off the sheet (as it is always full to col 256) comes up (and stops the code running). Deleting (or clearing the contents of) col 256 first does not overcome this unless the workbook is saved to reset "the last cell used". (This is what it says to do in the message)
(It works OK if I make a simple new sheet with data in 256 columns but not on mine)

I'm stumped!
 
Upvote 0
First, I wouldn't use UsedRange, it's not horribly reliable.
Second, How do you plan to move the data 1 column over, if you are using every single column?

If your willing to clear column IV, why not:

Code:
Sub test()
Columns(256).ClearContents
Columns(30).Insert
End Sub
 
Upvote 0
I will experiment with an alternative to UsedRange.

The code you suggest - to clear contents of Column 256 and then insert Column 30 - works fine if I recreate the situation in a new "small" workbook, but it won't work in my "big" workbook, even if I try it on add a new "simple" Worksheet.

It brings up this Run-time Error 1004
-------------
To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet.
Try to delete or clear the cells to the right and below your data. Then select cell A1, and save your workbook to reset the last cell used. Or, you can move the data to a new location and try again.
-------------

I've don't know why but I suspect it may be related to the complexity of my workbook.

My original code works fine, in that it eventually ends correctly, but I'd still like to know why it is "hanging" for 70 sec while trying to do the "Copy" operation on the second and subsequent runs through. (and Excel shows as "not responding" in Task Manager during that 70 sec)

There is something funny going on with my Workbook which gets "fixed" when it is close and reopened.
 
Upvote 0
Thanks again for your help HOTPEPPER

I have found why I could not delete Column 256, and insert a new column.

I had "whole row" Pattern formatting applied to the headings in Row 1.

Removing it fixed that problem.
I think I can now work around the other "hanging" problem by avoiding using Copy. (Although I'd still like to know why it happens)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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