Moving Row Data

[Donald Lloyd]

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
--

 

[Tom Ogilvy]

Sounds like something you could easily test out yourself Don. Let us know
what you find out, please.

Regards,
Tom Ogilvy

 

[Donald Lloyd]

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

--

Sounds like something you could easily test out yourself Don. Let us know
what you find out, please.

Regards,
Tom Ogilvy

 

[Henry]

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