merge cells

  • Thread starter Thread starter herman
  • Start date Start date
H

herman

how can i merge the info on a large spread sheet quickly
without having to do this one cell at a time.

I have info in cells A1 thru HH1,A3-HH3,A5-HH5 and so on
I need this info to be merged with the cell directly below
it

In other words I need cell A1 to be merged with A2,B1 with
B2....A3 with A4,A5 with A6 ....HH5 with HH6 with our
losing the info in the cells,
whats the simpliest and fastest way to do this with out
having to click on cell, hold shift key, down arrow,
merge cell button, right arrow, hold shift key,down arrow
key, merge button or format painter on completed a merged
cell, right arrow button, click format painter and repeat,
there's got to be an easier way I have cells AB18-HV18 down
to row 256 to do this to help me i'm getting carpal tunnel
syndrome. thanks in advance
Herman
 
You'll need to write a macro to do this; I don't know of any shortcut.

First, make a backup of your spreadsheet. Then, record the actions you do
for one pair of cells being merged, and stop the macro recorder. Then
examine the code and make it more general so it can go across a whole row of
your data and be included in a For...Next loop. Then enclose that code in
another For...Next loop to go down to row 256, doing the same thing.

For help with the macro itself, best place to post is in the .programming
group once you get started. (Post the actual code that's not doing what you
expect.)
--
HTH -

-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com
 
Give this a try on a back-up copy of your file:

Assuming your original data is in A1:HV256

Insert a new col to left of col A
Type in the namebox: A1:A257
(the one with the drop arrow, just to the left of the formula bar)
Press Enter
(this selects A1:A257)
Put in the formula bar: 0
Press Ctrl + Enter
(this puts 0's in A1:A257)

Put in A258: 1
Put in A259: 2

Select A258:A259, click copy
Select A258:A514, click paste
(this pastes alternating 1's and 2's in A258:A514)

Type in the namebox: B258:HW514
Press Enter
Put in the formula bar: =TRIM(B1&" "&B2)
Press Ctrl + Enter
(this will put all the merge formulae into B258:HW514)

Select rows 258 to 514
Click copy > paste special > check "values" > ok
(to convert all formulae to values)

Do a Data > Filter > Autofilter on col A

Select "2" to filter out all rows numbered "2" in col A
Select all these rows > right-click > delete rows
(these are the rows you don't want)

Switch-off the Autofilter

The rows numbered "1" in col A should contain the merged data that you want.

Select > Delete col A if desired

HTH
 
Back
Top