Merge Cells insert rows

[Partjob]

Hi
I am haveing probems inserting rows through a range of merged cells. I can do the task normally. Select row insert row. However if I record the action I do not get the same result when I run the code.
I know that merged cells are the bain of VBA and the two generally do not get on.
Firstly is there a work around?
The sheet I am working on is not my design I have just been tasked with dealing with a few problems.
There are twelve sections to this sheet and the merged cells in question form a banner down the left side for each section. All are of differing lengths. I have tried to unmerge these cells and just have the title in one cell. The text is orentated vertically and does not run in to the next cell as it would when horizontally.
This banner used to be on one row at the top of each section but I wanted a continous run of cells for formulas and formats so I moved it only to run in to more problems.
Ideally I need a work around for the insert row through the merged cells but would live with a way of having my section banner run across multiple cells vertically without the cells being merged.
Thanks Partjob

 

[Mike Blackman]

Hi,

The workaround would be not to Merge cells as it just causes problems. Instead of Merging the cells you can highlight several cells with the cell that holds data and format the cells - allignment Tab - Horrizontal Aliignment - Center across selection.

 

[dscg]

You'll have to tweek to meet your ranges, but unmerge remerge seems like a decent plan.

Sub Macro1()
Range("A1:C5").UnMerge
Rows("3:3").Insert Shift:=xlDown
Dim i As Long
For i = 1 To 5
Range("A" & i & ":C" & i).Merge
Next i
End Sub

 

[Partjob]

I had thought about merge / unmerge which I could do, does seem a bit messy. As for centre across selection which I have used before and like, is unavalable when the text is vertical which is a shame.