• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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?

Thanks
0
GordonMasson
Asked:
GordonMasson
  • 2
  • 2
1 Solution
 
Dave BrettCommented:
Gordon,

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

Cheers
Dave

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

0
 
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?

Thanks
0
 
Dave BrettCommented:
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?

Dave
0
 
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.

PS
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now