Clear cell contents if not the target cell

[mscott123]

Hello,

I have a code that references over 100 columns, and 9 rows deep. Basically, when a cell is double-clicked in one of these columns, the other row's cells in that column are erased. (A mutually exclusive 'selection').
The only problem is I want to add hidden columns and move ranges around and I'm wondering if there is a faster way of re-writing the code than repeating the following 'cases' 100 times (see,somewhat inelegant). Any ideas are most welcome!

Cheers!

M

Select Case Target.Address
Case Is = "$E$5", "$E$6", "$E$7", "$E$8", "$E$9", "$E$10", "$E$11", "$E$12", "$E$13"
'Clear Contents of cells that are not the target
'Asset 1, scenario 1
If Target.Address = "$E$5" Then [E6:E13].ClearContents
If Target.Address = "$E$6" Then [E5,E7:E13].ClearContents
If Target.Address = "$E$7" Then [E5:E6,E8:E13].ClearContents
If Target.Address = "$E$8" Then [E5:E7,E9:E13].ClearContents
If Target.Address = "$E$9" Then [E5:E8,E10:E13].ClearContents
If Target.Address = "$E$10" Then [E5:E9,E11:E13].ClearContents
If Target.Address = "$E$11" Then [E5:E10,E12:E13].ClearContents
If Target.Address = "$E$12" Then [E5:E11,E13].ClearContents
If Target.Address = "$E$13" Then [E5:E12].ClearContents

[Mumps1]

Are the 100+ columns consecutive? If so, what is the column range?

[mikerickson]

One way would be

Please Login or Register  to view this content.

[Mumps1]

This macro in the worksheet code module should work for any column as long as you don't double click in a column that is not in your 100+ columns.

Please Login or Register  to view this content.

[mscott123]

Super, thanks Mumps1 and mikerikson. The columns are, unfortunately, discontinuous, my apologies, I should have mentioned this. Mikerikson's first response is likely going to be the way i go, as I'm afriad of overwriting formula linked values which separate the target columns. Although props mumps1, very clever solution!!

[Mumps1]

If the target columns are always the same, you could restrict the clearing of the cells to only those columns. I would need to know which columns.

[mscott123]

I see... the pattern is three columns of formulas, followed by two columns relating to this code, starting in column D. There are other non-mutually exclusive double-click rows below the ones being discussed however, in those columns so the row range would still need to be restricted.

[mscott123]

Full disclosure... in this lower column section with non-mutually exclusive marlett font 'checks', I also use about a bajillion lines of code like this (to help speed up user driven selection) across multiple rows in the same column. Not sure if there is a better way for this to happen either...

Case Is = "$E$22"
Range("$E$23:$E$37") = Target.Value
Case Is = "$E$40"
Range("$E$41:$E$56") = Target.Value

[Mumps1]

Would it be possible to attach a copy of your file including any macros you are currently using, de-sensitized if necessary? This would make it easier to see how your data is organized and test possible solutions.

[mscott123]

Hi Mumps, yeah let me upload something...

[mscott123]

Here is an example of the 'scenario dashboard.' The whole idea is to make this user friendly, but my code is kind of ugly. There will need to be about 50 of these assets built out so you can see how the string gets very long...

Many thanks,
m

[Mumps1]

I'm a little bit confused. The code that I suggested was a Worksheet_BeforeDoubleClick event while in your file, it seems to be in a Worksheet_Change event. Do you want the cells cleared on a cell change or on a double click? Please clarify.

[mscott123]

Sorry for the confusion.
I thought because I already have the worksheet_beforedoubleclick event in use (for the non-exclusive selection of cells and as it applies to all row ranges in the columns) I could specify a separate _change event just for the mutually exclusive cells. I'm actually happy with all the functionality as is demonstrated, but just not excited about the long code string over 100 columns of mutually exclusive change event and mutually non-exclusive before double click events.

[Mumps1]

Try the attached file. It should take care of all the appropriate columns.

[mscott123]

Thanks Mumps1!

[Mumps1]

My pleasure.

[mscott123]

I suppose the only problem is that while the code is much tidier, it actually runs 3x slower than then explicit formulas originally used... Any ideas?

[mscott123]

yeah. It's just too slow so I have changed it back.
is there a way to protect the hidden outlined columns from selection? (They are included in the double click named range, I can't exclude them because otherwise here would be too many ranges selected under the same named range...)

[Mumps1]

I'm not sure what you mean by

is there a way to protect the hidden outlined columns from selection?

Can you explain in detail?

[mscott123]

Sorry, to make matters more complex I had some hidden columns in between each intended rows cell that were hidden via 'outlining' (grouped columns) which I didn't wish to overwrite.
I did figure out how to leave those ones alone with this:
If target.HasFormula Then Exit Sub

I am, however, considering getting rid of this nice user interface altogether, because as my model grows employing the doubleclick interface vba seems to get slower and slower...
Sorry mumps, nothing wrong with your code, just my modeling!

[Mumps1]

So is everything OK now?

[mscott123]

Yes, thank you

[Mumps1]

Good to hear.