Merge Cells

[Dominique Feteau]

Can someone help me out with a macro that will merge & center a number of
cells?

Data looks like this

Column A Column B
1. ABC Company Category 1
2. Category 2
3. Category 3
4. Category 4
5. Category 5
6. Category 6
7. XYZ Company Category 1

and so on and so forth. What I want to do is to have a macro merge & center
A1:A6, then move on to A7:A12.

Can someone help me out?
Thanks

 

[David McRitchie]

Hi Dominique,
If I were you I wouldn't do that. It's a lot easier to
read it the title is at the top of a merged cell (IMO).

It would make it so you can sort rows.

You could include the company in each cell in column A
and use Conditional formatting to make it "disappear" or
be lighter if equal to the cell above.

This will do what you asked for merging
change xlTop to xlCenter if that's what you really want.

Macro cannot be rerun, suggest testing on a copy of your worksheet.

Sub Macro1()
With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With
On Error GoTo done
Cells(Rows.Count, 2).End(xlUp).Activate
ActiveCell.Offset(0, -1).Activate
backthere:
Range(ActiveCell.Address, ActiveCell.End(xlUp).Address).Select
Selection.Merge
ActiveCell.Offset(-1, 0).Activate
GoTo backthere
done:
End Sub

--

 

[SidBord]

I don't completely understand what the end result will look
like, but you might want to explore the use of something like:
Worksheets(sheetname).Columns("AE").EntireColumn.AutoFit