Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Row Data
Hi,
It just ocurred to me. Example:- In a worksheet, the top 2000 rows by 50 columns are used to hold data that is being manipulated. The cells in this range are all carefully formatted. All rows below this range are hidden and have not been formatted, as I, maybe wrongly, think that this can increase the Used Range and result in undue overheads. Query:- If cell contents are deleted using Shift(XlUp), does this mean that the 2000 rows will lose their formatting from the bottom up ? If yes, what is the best solution. In the same vein, but more academic, if entire columns are formatted, will the formatting be gradually eroded from the bottom up if cells are shifted up or rows deleted ? regards, Don -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Row Data
Sounds like something you could easily test out yourself Don. Let us know
what you find out, please. Regards, Tom Ogilvy Donald Lloyd wrote in message ... Hi, It just ocurred to me. Example:- In a worksheet, the top 2000 rows by 50 columns are used to hold data that is being manipulated. The cells in this range are all carefully formatted. All rows below this range are hidden and have not been formatted, as I, maybe wrongly, think that this can increase the Used Range and result in undue overheads. Query:- If cell contents are deleted using Shift(XlUp), does this mean that the 2000 rows will lose their formatting from the bottom up ? If yes, what is the best solution. In the same vein, but more academic, if entire columns are formatted, will the formatting be gradually eroded from the bottom up if cells are shifted up or rows deleted ? regards, Don -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Row Data
Sorry Tom!
It was an off-the-cuff query. I have in fact tested it and report as follows:- Formatting only some of rows results in the formats being moved up if cells are moved up or rows deleted. Curiously, moved / deleted formats are repeated from row 65536 upwards each time. If entire columns are formatted, the problem doesn't arise - also, I checked that formatting entire columns does not alter the UsedRange, so I can go to bed happy.:) The above applies to uniform formatting of all rows - different formats on different rows would be a problem. Thanks for the excercise - I needed it! it's taken me longer to draft this post than it did to do the tests! regards, Don -- "Tom Ogilvy" wrote in message ... Sounds like something you could easily test out yourself Don. Let us know what you find out, please. Regards, Tom Ogilvy Donald Lloyd wrote in message ... Hi, It just ocurred to me. Example:- In a worksheet, the top 2000 rows by 50 columns are used to hold data that is being manipulated. The cells in this range are all carefully formatted. All rows below this range are hidden and have not been formatted, as I, maybe wrongly, think that this can increase the Used Range and result in undue overheads. Query:- If cell contents are deleted using Shift(XlUp), does this mean that the 2000 rows will lose their formatting from the bottom up ? If yes, what is the best solution. In the same vein, but more academic, if entire columns are formatted, will the formatting be gradually eroded from the bottom up if cells are shifted up or rows deleted ? regards, Don -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Row Data
Don,
If an entire column is formatted then all the cells from 1 to infinity are formatted. You can only see 1 to 65536. If you delete rows, the entire column is still formatted down to 65536 (and beyond). Try it yourself. On a new spreadsheet select entire col. A. Fill with a colour. Go down to row 65536. Select rows 65530-65535 and delete them. A65536 will still be coloured. HTH Henry "Donald Lloyd" wrote in message ... Hi, It just ocurred to me. Example:- In a worksheet, the top 2000 rows by 50 columns are used to hold data that is being manipulated. The cells in this range are all carefully formatted. All rows below this range are hidden and have not been formatted, as I, maybe wrongly, think that this can increase the Used Range and result in undue overheads. Query:- If cell contents are deleted using Shift(XlUp), does this mean that the 2000 rows will lose their formatting from the bottom up ? If yes, what is the best solution. In the same vein, but more academic, if entire columns are formatted, will the formatting be gradually eroded from the bottom up if cells are shifted up or rows deleted ? regards, Don -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
Moving data from one worksheet to another whilst removing the data | Excel Discussion (Misc queries) | |||
moving data to non autofarmatted book changes data- Why? | Excel Discussion (Misc queries) | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) |