Copying Cells

elgringo56

Well-known Member
Joined
Apr 15, 2002
Messages
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top