Copying Range Just To Get Formatting?
 

I've got a "WhatIf" section on a spreadsheet where the user can
choose a couple of entities via .Validation drop down boxes.

When one entity is chosen but the other is not, I need to clear
the contents of many cells in that row.

..Range.Clear doesn't work because it clears the borders of the
cells too.

Or am I missing something in .Clear?

If not, it seems like one approach would be to create a model
range in an invisible row - that has the borders desired - and
copy said model over the WhatIf columns when it's time to clear
them.

But I've got a chicken-egg problem with the model: only place I
can get it is a place on the sheet where cells are already
populated with values.... but .sourceRange.Copy targetRange
doesn't seem to have a provision for copying only formatting and
leaving the data behind.

I'm striking out on .PasteSpecial - although it *seems* like it
should do the job with the right parms specified.

Can anybody offer a suggestion? Am I barking up the wrong tree
here?


I do not want to explicitly format the model range because when
the sheet is created via VBA, the user can specify options that
affect the layout of the rows - hence the "model" approach.
--
PeteCresswell

Copying Range Just To Get Formatting?
 

Use .ClearContents
--
Pops Jackson


"(PeteCresswell)" wrote:

I've got a "WhatIf" section on a spreadsheet where the user can
choose a couple of entities via .Validation drop down boxes.

When one entity is chosen but the other is not, I need to clear
the contents of many cells in that row.

..Range.Clear doesn't work because it clears the borders of the
cells too.

Or am I missing something in .Clear?

If not, it seems like one approach would be to create a model
range in an invisible row - that has the borders desired - and
copy said model over the WhatIf columns when it's time to clear
them.

But I've got a chicken-egg problem with the model: only place I
can get it is a place on the sheet where cells are already
populated with values.... but .sourceRange.Copy targetRange
doesn't seem to have a provision for copying only formatting and
leaving the data behind.

I'm striking out on .PasteSpecial - although it *seems* like it
should do the job with the right parms specified.

Can anybody offer a suggestion? Am I barking up the wrong tree
here?


I do not want to explicitly format the model range because when
the sheet is created via VBA, the user can specify options that
affect the layout of the rows - hence the "model" approach.
--
PeteCresswell