Merging Cells

  steviegee 15:00 17 Jan 2008
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 2008

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 2008

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 2008

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 2008

Sorry, I misread the question.

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

  steviegee 15:28 17 Jan 2008

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 2008

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 2008

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

  VoG II 15:39 17 Jan 2008

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 2008

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 2008

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.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?