Purge / Delete Unused rows and Columns in Excel 2007 and copy Column

Can you tell me how you purge / delete all unused columns and rows in Excel 2007 please?

I have a worksheet where I want to copy and insert a column and I use this code

    Range("SortContacts").Offset(0, -1).EntireColumn.Copy
    Range("SortContacts").Offset(0, 1).EntireColumn.Insert Shift:=xlToRight

The second line seems to take a very long time to execute and I am assuming (perhaps wrongly) that its something to do with the increased number of columns in 2007?

Also if you pull the tab on the horizontal scroll bar to the right it goes all the way out to column WTC rather than just the range of the data in the sheet.

Any ideas please or should I be using different code to insert this column in 2007?

Who is Participating?
Normally its done by forcing Excel to recount the rows in the UsedRange. David McRitchie has a number of solutions at http://www.mvps.org/dmcritchie/excel/lastcell.htm

This shouldn't be your issue though

Does running the code with Calculation off etc improve matters?


Does this code which uses only the use protion of the colum to be copied work quicker?


Dim rng1 As Range
Set rng1 = Range(Cells(1, Range("SortContacts").Column - 1), Cells(Rows.Count, Range("SortContacts").Column - 1).End(xlUp))
Cells(1, Range("SortContacts").Column + 1).EntireColumn.Insert
rng1.Copy Cells(1, Range("SortContacts").Column + 1)

Open in new window

GordonMassonAuthor Commented:
Hi Dave
Thanks for getting back to me ... thats pretty much the same im afraid but if you run either code in a new worksheet its pretty instantanious, its just because the sheet thinks there are hunreds of active columns ... i think???

In a new sheet the horizontal scroll bar when dragged to the right stops at about column Q but on my other sheet it goes way out to column WTC so i think its something to do with unused columnsall having to be shifted to the right to insert the new column?

Is there a way to reset the used range of the worksheet to only include the cells with data in them?

GordonMassonAuthor Commented:
OK that works thanks Dave
This makes the work sheet as fast as a blank sheet now and resets the scroll bars so that only the used cell ranges are covered when dragging it from left to right.

Thanks again for your help.

I think i have another question about Outlook 2007 that i may need your help with ... would appreciate it if you can help with that when  post it later
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.