Merge cells based on a cell value

[ChristineJ]

I would like to merge cells C through F on each row where the value in cell A is 5. Can that be done?

[p45cal]

Yes.

1.Using formulae on the worksheet or as a macro?
2.When you say merge, merging cells in excel usually only keeps the text in the leftmost cell - do you mean concatenate? If so where do you want the result?

the penalty for asking a brief question.. a longer response, without the solution.

[ChristineJ]

Thanks for the response.

I don't want to concatentate values in cells; I actually want to merge cells C through F so they are one big cell.

I understand that only the data in the left-most cell is retained in a merge, but in my case the merge will actually occur before any data is entered.

I believe I will need VBA for this.

[p45cal]

The following will work on the rows of the selected cells (the selection doesn't have to include column A) of the active sheet:
[vba]Sub blah()
For Each rw In Selection.Rows
If Cells(rw.Row, 1) = 5 Then 'column A of each row
Range(Cells(rw.Row, 3), Cells(rw.Row, 6)).Merge
End If
Next rw
End Sub
[/vba]

[ChristineJ]

I got it working!

Thanks so much!!!!