Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Clemmons, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too many columns? (Excel 2000)

    While running a large macro that inserts quite a few columns, I get an error message that I am trying to get around. (See attached for picture of msg box)
    How can I find out what the last column/row used is, and how can I reset it without saving the file right in the middle of my macro?

    Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too many columns? (Excel 2000)

    Activesheet.Usedrange
    resets the used range without the need for saving. You can check
    Activesheet.Usedrange.address to see wheter inserting columns will push some off the sheet or not.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too many columns? (Excel 2000)

    Sometimes, Excel considers cells to be nonblank although they don't contain a formula or value. This can happen if you have formatted entire rows or columns. Moreove, Excel doesn't always reset the last used cell during a session in which lots of rows or columns are being inserted and deleted.

    There is a way to force Excel to determine the last cell correctly: simply refer to ActiveSheet.UsedRange.Rows.Count or ActiveSheet.UsedRange.Columns.Count. See John Walkenbach Tip#73.

    Example:

    Dim lngLastCol As Long
    lngLastCol = ActiveSheet.UsedRange.Columns.Count

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •