Use the following code, by right clicking on the sheet tab, then 'View Code' and paste this code into the VBA editor.
I have also re-attached your sample workbook with the code loaded in it.
NOTE I have Un-merged cells H1 and H4, and instead formatted them as 'center-across-selection'.
Merged cells are the bain of VBA coders as they cause problems with many of VBA's functions, (least of all you can't lock/unlock merged cells). It is generally better to leave the cell un-merged.
If you have other cells on your actual sheet that you need to do this to then follow these steps.
1) Select a merged cell.
2) Unmerge the cell.
3) Select the cells that previous made up the 'merged cell'
4) Select 'Format' --> 'Cells'
5) From the Alignment tab, under horizontal select 'Center across selection'.
Doing this means that the data will only be in cell H1, (or H4), as far as VBA is concerned, but to the user it will still look as though the cell is merged
If you have any questions please do ask.
Bookmarks