VBA + SelectionChange event

**HuskerBronco** · 02-13-2011, 08:16 AM

Hello all....

I'm sure I need to use some sort of OFFSET in my VBA code but I can't find an example of what I need.

Here is what I am trying to do:

Suppose I have a Named Range..."TrueFalse" = (Sheet2!A1:A10,Sheet2!E1:E10,Sheet2!I1:I10)

Each cell in that Named Range can be either TRUE or FALSE.

If TRUE...I want the 3 cells to the right of each cell to be merged.
If FALSE...I want the 3 cells to the right of each cell to be UNmerged.

As the value in each cell is changed, I want the MERGE to change as well.

I think I am on the right track placing this into a SelectionChange Sub in Worksheet 2....
I just don't know how to construct the code. (Or am I way off base??)

Any suggestions...???

**davesexcel** · 02-13-2011, 08:28 AM

Why do the cells need to be merged?

**HuskerBronco** · 02-13-2011, 08:46 AM

The spreadsheet is for a TV schedule.

Say, for example...

Cell B1 would belong to a 1/2 hour time slot (from 7 PM to 7:30 PM)
Cell D1 would belong to the 7:30 PM to 8:00 pm time slot.

TRUE in Cell A1 would indicate an HOUR long program.
So, I want Cells B1:D1 to merge, and the program title to be centered across the selection.

FALSE in Cell A1 would UNmerge the cells.

Then the schedule will look less cluttered when printed out.

**davesexcel** · 02-13-2011, 11:02 AM

Try this

Please Login or Register  to view this content.

**snb** · 02-13-2011, 11:11 AM

Please Login or Register  to view this content.

**HuskerBronco** · 02-13-2011, 02:14 PM

Thank you, very much...guys!!! (davesexcel & snb)

You both replied with nearly identical code.

snb...I still haven't gotten the RESIZE function into my VBA mindset.
I've used the function before, but never think about it when writing new code.

davesexcel...While searching the internet for this code, I ran into a great deal of
conversation about the EVIL of merging cells in Excel spreadsheets.
I seems that the general consensus is that you should always try to AVOID this practice.
Can you give me some insight as to why this is considered "taboo".

Thanks, again...to both of you, for your help.

**shg** · 02-13-2011, 03:40 PM

why this is considered "taboo".

In most cases, merged cells require special handling in VBA.

In the general case, you can’t sort a table that contains merged cells.

Lookup (and many other) functions don’t work if the referenced ranges contain merged cells.

Merged cells should be reserved for presentation, and should be output-only; i.e., they should not be referenced by other formulas, and not be used (except perhaps to be written) by VBA.

**snb** · 02-13-2011, 04:06 PM

Generally speaking the practical feature of VBA is you can refer directly to a cell, column or row to read/write/manipulate.
With merged cells VBA gets confused (which cell, which row, which column). The last thing you want to be confronted with in programming is confusion: digital/binary systems are 'confusionfobic'.

**HuskerBronco** · 02-13-2011, 06:36 PM

Originally Posted by shg

In the general case, you can’t sort a table that contains merged cells.

Lookup (and many other) functions don’t work if the referenced ranges contain merged cells.

Now I understand.

I've had numerous instances in the past where merged cells have "broken"
or "crippled" my formulas.

Never realized, until now, that I was causing myself so much grief.