Merging Cells

  steviegee 15:00 17 Jan 08
Locked

I have a spreadsheet with lots of data on. I want to merge 2 celss together without having to do them one by one i.e a1 with a2 b1 with b2 etc Is it possible/ I cant copy each cell as they all have data in. Thanks.

  VoG II 15:10 17 Jan 08

In C1 enter

=A1&B1

then drag the formula down as far as needed. Select column C then Copy, Edit > PasteSpecial and select Values. Then delete columns A and B.

  steviegee 15:16 17 Jan 08

I dont think that will work. It is more of a table/timesheet with headers on the columns. A1/A2 name, B1/B2 hours on monday, C1/C2 hours on tuesday etc etc. At the moment I highlight A1/A2 select format cells> alignment> merge> centre but having to do them one at a time seems very time consuming.

  steviegee 15:19 17 Jan 08

I dont think that will work. It is more of a table/timesheet with headers on the columns. A1/A2 name, B1/B2 hours on monday, C1/C2 hours on tuesday etc etc. At the moment I highlight A1/A2 select format cells> alignment> merge> centre but having to do them one at a time seems very time consuming.

  VoG II 15:20 17 Jan 08

Sorry, I misread the question.

Merging will 'lose'one of the values - is this what you want to do?

  steviegee 15:28 17 Jan 08

There is nothing in even numbers A2 A4 etc all the info is in odd A1 A3 etc I want to merge 2 cells and centralize it all so it looks neater. And like I say it is all across the columns up to about h.

  VoG II 15:33 17 Jan 08

Try this on a COPY of your sheet.

ALT + F11 to open the Visual Basic Editor. Insert > Module. Copy and paste in

Sub mrg()
Dim LastRow As Long, iRow As Long, LastCol As Integer, iCol As Integer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Application.DisplayAlerts = False
For iCol = 1 To LastCol
For iRow = 1 To LastRow Step 2
Range(Cells(iRow, iCol), Cells(iRow + 1, iCol)).Merge
Next iRow
Next iCol
Application.DisplayAlerts = True
End Sub


Close the VBE. Select the copy of your sheet, Tools > Macro > Macros, highlight mrg and click the Run button.

  steviegee 15:39 17 Jan 08

That only worked on column A and didnt put the values vertically centre.

  VoG II 15:39 17 Jan 08

If you want them centred as well as merged:


Sub mrg()
Dim LastRow As Long, iRow As Long, LastCol As Integer, iCol As Integer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Application.DisplayAlerts = False
For iCol = 1 To LastCol
For iRow = 1 To LastRow Step 2
With Range(Cells(iRow, iCol), Cells(iRow + 1, iCol))
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
Next iRow
Next iCol
Application.DisplayAlerts = True
End Sub

  VoG II 15:40 17 Jan 08

Make sure that there are values in row 1 of each column that you want to merge. You can always delete them later.

  steviegee 15:42 17 Jan 08

We are almost there! Does it good on Column A just need it to do it on the rest of the columns. Thanks VoG.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now