excel cannot shift nonblank cells

[NicholasP]

I'm trying to insert several columns but I get an error about Excel not being able to shift non-blank cells off of a sheet. The problem is, the cells outside my small range (goes to about column W) are blank...when I push CTRL+END I get UV120....

I see there is a work-around here:

http://www.mrexcel.com/forum/showthread.php?t=471031

My problem is the same as the original poster; It is very cumbersome to delete all cells to the right of my range, save the workbook and re-open it. This would be a great one-time work around, but I receive this workbook daily from a 3rd party and so this problem will pop up daily. Is there a solution that does not require me to use the delete/save/reopen work-around?

Thanks
Nick

 

[Joe4]

The easiest way would be to get whoever is sending your the file to make the correction on their end (maybe they are formatting the entire spreadsheet instead of just the range they need).

If that is not possible, I would probably open up the file, and copy and paste the appropriate range to a new, blank worksheet and then work from there. You could even make this part of a macro so you wouldn't have to do it manually (possibly along with whatever else you are doing with moving the data around).

 

[NicholasP]

I guess I could incorporate that as part of my macro. I was really hoping there was less cumbersome solution though. Thanks for your help.

Nick

 

[Joe4]

I guess I could incorporate that as part of my macro. I was really hoping there was less cumbersome solution though. Thanks for your help.

Shouldn't be cumbersome at all, especially if it is part of your macro.
If all the data is contiguous so that you can use CurrentRegion, it should be even easier yet.