Merging cells

[ilawton]

Hi all

I don't understand an awful lot about excel so really don't know if my problem can be solved.

I have a large spreadsheet with about 2000 rows and about 20 columns. What I am trying to do is merge cells every 3 rows for each column. I am a complete noob and the 'quickest' way I can find is by selecting the 3 rows and clicking on the merge cells button. This is doing my head in, as you can imagine its taking me hours and I am only part way through what I need to do. Is there any way I can select multiple rows and columns and specify where I want the merges to take place? Ie select column C and carry out a merge cell every 3 rows eg C6-8, C9-11, C12-14 etc etc? I have hunted on the internet but don't seem to have found anything relating to what I need to do. Any advise is very much appreciated. Thanks for your time.

[oldchippy]

Hi,

May be this might help? If you do column C, C6-8, C9-11, C12-14 etc etc. say down 10 groups of three to start with. Then select C6:C33, double-click on the Paintbrush icon, then select C36, then C66, then C96. Then click again on the Paintbrush icon to switch it off, then select C3:C123 and do the same again down the column.

[ilawton]

Hi, thanks for your response. That doesn't work as I have different data in each 3rd cell and your suggestion basically means I am copying and pasting the same info.

[oldchippy]

Right OK, then can't you do a similar thing but not using the Paintbrush but copying and Paste Special > Formats

[ilawton]

When I have tried that then the cell I paste into become blank.
Any other suggestions? Thanks

[shg]

My suggestion is that you not merge cells. It's not good practice, and very difficult to deal with if VBA ever needs to operate on the data.

[ilawton]

I have to merge the cells or it means redoing the whole spreadsheet again. I won't ever require using vba on this spreadsheet. If there is no solution then I will continue plodding away, thanks anyways.

[royUK]

If you merge cells then you won't be able to use any of Excel's Database features. I think Merged Cells is one feature that MS should remove.

Why do you need to Merge these cells?

[oldchippy]

When I have tried that then the cell I paste into become blank.
Any other suggestions? Thanks

OK, tell me how your first page looks like in column C without any merged cells. Which cell has a value in, is it C6, C9, C12 etc. etc.....every third cell?

[ilawton]

I am trying to attach a smaller copy of the current spreadsheet and then the one where I am trying to get to but I don't seem to be able to attach anything. I thought that might help explain but anyways it won't let me attach anything.

The cells that contain value are C8, 11, 14, 17 etc.

Cheers

[oldchippy]

In that case if your value is in C8 and you merge C6:C8 the actual visable cell (of that group) is C6, so your value "disappears". But in fact it is hidden from view.

So if you move the value first of all to C6, then merge C6:C8, you should now see your value.

If you want to attach a file try this.....

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

[ilawton]

Right OK, then can't you do a similar thing but not using the Paintbrush but copying and Paste Special > Formats

By the way I tried something a little different when trying the different options in paste special. I created a new spreadsheet with the correct amount of rows and then copied stuff from my other spreadsheet and paste special > all except borders, and that worked to some extent. The only problem though is where I have an address for example in one of my rows and pasted it that over to the new cell with 3 rows it chops the top and bottom off the text. The only way I know around that is once again merging the cells.

[ilawton]

Old Chippy you're a star mate! I have moved the date into the top cell as you explained and that works. Thanks ever so much for your help, really appreciated.

[oldchippy]

Glad to hear you got it in the end - but just be aware of the warnings from shg and royUK about working with merged cells.

[ilawton]

Well, something which took up alot of my time at work yesterday, at home last night and over 2 hours this morning has now been completed in a matter of minutes. I appreciate the comments regarding merged cell problems but this is a work spreadsheet and I have to transfer a shed load of information in this merged cell format. My info wasn't contained this way and the amount of time I have spent trying to do this was seriously doing my head in. Thanks for all responses. Cheers