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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

FredMFoley

Board Regular
Joined
Mar 18, 2002
Messages
58
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!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

FredMFoley

Board Regular
Joined
Mar 18, 2002
Messages
58
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.
 

FredMFoley

Board Regular
Joined
Mar 18, 2002
Messages
58
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,373
Messages
5,571,768
Members
412,419
Latest member
acemali
Top