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

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
>