Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Copying Cells

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Haveing a problem understanding something. I have an area that is 6 columns wide and 10000 rows long. this area contains an unknown amount of data at the top of it. All the cells within the area below the data are blank. I copy the entire area and move and paste it on another sheet beginning at cell A1. I then do a "ActiveSheet.UsedRange.Select", expecting only the cells with data in them to be selected. But, all the cells within the 10000 row area get selected. Am I doing something wrong?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi elgringo56,

    I won't claim to know exactly how the UsedRange property or a worksheet gets set, but one thing I do know is that, contrary to what many people think, a range is not just "used" when it contains data (i.e., the cell value is non-empty). It is also "used" if any cells in the range are formatted (including number formats, colors, etc.), contain comments, hyperlinks, validation, conditional formatting, etc. Once a cell has had one of these other properties changed, even doing a ClearContents on the cell will not remove it from the UsedRange. So if you copy cells from another sheet, and these cells have in the past been formatted, they will become part of the UsedRange on the destination sheet even if they don't contain data.

    Perhaps some of the other "Board Members" more knowledgeable than I in this area will provide additional insight.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try ActiveCell.CurrentRegion.Select


    didn't you ask that exact same question yesterday?

    [ This Message was edited by: brettvba on 2002-04-28 21:07 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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