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

Posted on 2010-01-07
Last Modified: 2012-05-08
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?

Question by:GordonMasson
    LVL 50

    Expert Comment

    by:Dave Brett

    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


    Author Comment

    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?

    LVL 50

    Accepted Solution

    Normally its done by forcing Excel to recount the rows in the UsedRange. David McRitchie has a number of solutions at

    This shouldn't be your issue though

    Does running the code with Calculation off etc improve matters?


    Author Closing Comment

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now