Copying Cells

elgringo56 · Apr 28th, 2002, 07:09 PM

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?

Damon Ostrander · Apr 28th, 2002, 09:38 PM

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.

brettvba · Apr 28th, 2002, 10:06 PM

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 ]

Apr 28th, 2002, 09:38 PM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	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 (My other life: http://damonostrander.com )

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 28th, 2002, 07:09 PM	#1
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	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?

Apr 28th, 2002, 10:06 PM	#3
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	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 ]