Merge Column Cells With Same Value Automatically

I'm trying to create a basic Gantt Chart to plan our future workload for our estimating dept. I would like to be able to automatically merge the cells containing the same year (ie on row 2) and also automatically merge the cells containing the same month (ie on row 3).

Result would be that H2:AI2 would be merged and contain "2012". Cells H3:J3 would be merged and contain "Oct". Cells K3:AI3 would be merged and contain "Nov".

Thanks in advance.

Matt

Hi

One of the forum gurus may have a better solution, but i can think of 2 ways to do this.

The easiest way is with a macro converting the formulas to values and deleting the replicated values, then merging. But this would lose the dymanic link to your start date in F1.

A slightly more complicated version would re-create the unmerged cells when F1 changes, then re-merge them

have you a preference for one of these?

Hi Nicky,

Either way would be acceptable. All I need to ensure is when I enter the new date into cell F1 (for the start of the Gantt Chart period), the year and months are merged over the appropriate cells. It is only to provide a cleaner layout when printed so that senior management understand better (and look less cluttered).

I would also like the merged cells to be centred too if possible.

Thanks.

Hi
in that case you need option 2 I think - otherwise, when you change the start date, the merged cells will not change to refect the new date
I'll have a go and see what I can come up with

Hi
try this
it unmerges the cells and re-inserts the formulas before begning a new merge, so you should be able to run it to update your gantt chart whenever the date changes

Please Login or Register  to view this content.

Hi Nicky,

Thanks for your help. I copied and pasted your code after the other code that have for that worksheet and then hit save. Closed down code screen and retirned to spreadsheet and typed a new date into cell F1 and nothing happened. I returned to code sheet and hit the run button, closed down code sheet and returned to spreadsheet and a window had appeared saying "$AI$2" with an OK button. Hit OK and ended up with what I wanted. Great!

Tried changing date and that's when things started going funny. Months did not align with respective columns, weren't merged at the right locations etc.

Have I done something wrong with entering the code into workbook? I'm not too conversant with VBA and am a little out of my depth here. Would it be too much to ask if you could please enter code into workbook and post your copy back to me?

Thanks again. Matt

hi
sorry about the message box - it's left over from when I was testing the code. I sugget you detete that line:

Please Login or Register  to view this content.

every time you change the date you will need to re-run the macro. If you like, we would set it to run automatically, but that's a bit more complicated and I was keeping it simple. Does once you change the date and re-run the macro, does it look ok?

Hi Nicky,
I have deleted message box line. No messages now. Great.
Your code works great for the first time. Does exactly what I want.
When I enter a new date, it remerges as planned however the border disappears between the two merged ranges. Can that be fixed?
Also, is it possible to have the code run automatically after you've entered date when you hit ENTER?
Sorry to be so difficult. It's just that it will be used by others and I want to keep it really simple if possible.
Cheers.

Hi
to run it automatically every time the cell F7 changes, add this code to the worksheet_change event code for the worksheet:
(right click the tab with the sheet name and select 'view code' - there is already a worksheet_change event there setting colours)

Please Login or Register  to view this content.

this modified version sets a border around the month and year cells:

Please Login or Register  to view this content.

Hi Nicky
Sorry but I lost you with the first part of your last reply. Where do I need to insert this new code? I've tried in a few areas without any luck and without being a VBA wizz like yourself am struggling
I have replaced the 2nd part though and it remerges with new borders perfectly. I am having to manually run macro though for this to occur.
Are you able to copy the entire code for this sheet for me so I can copy and paste over the existing code in my worksheet?
Thanks again.

Hi Matt

to run the code automatically you need to run it from the code for that particular sheet, not into the general module that applies to all sheets in the workbook. To do this:

• right click on the tab with the sheet name GANTT at the bottom of the sheet.
• Select view code – this will take you into the code area for that sheet.

You will see there is already a private sub in there called:
Worksheet_Change(ByVal Target As Range)

This is a macro that runs automatically when the sheet is changed. We also want our macro to do that, but because there can’t be two worksheet_change nmacros we’re going to have to modify the existing one rather than adding our own.

To do this, scroll down to the bottom of the macro, and just above the end sub insert this code

Please Login or Register  to view this content.

This check to see whether cell F7 has been altered, and if so, it runs the macro gantt_update.

I have uploaded a version with these modifications, so you can see how they work in practice.

Just remember you need both the macro in a normal module and the automation on the sheet’s code for this to work.

Cheers

Nicky

Nicky
Have added code as per your example but now comes up with error?
Can you please have a look at my file? Note what happens when you enter new date into cell F1 and then hit enter.
Thanks.

ok - I suggest a couple of changes

instead of having the extra code at the end of the worksheet_change event, move it to the very to so it runs before the colour changes. So the top of the macro now looks like:

Please Login or Register  to view this content.

The gantt_update macro needs to be in a general module, not the code for the sheet. Cut it from its current location and paste it into a new module. To create a new module, type Alt+F11 (if you are in the worksheet) and select insert > module.

Nicky,
It all works - just like how I dreamt it could!
Many thanks for your help (and patience)
Cheers
Matt