Working with Merged Cells

[raeannb]

I've read all about how merged cells are the devil and no one should use them, but unfortunately someone more important than I am has decided that merged cells are prettier, and therefore are necessary. Hence: my problem. I hope I can explain this without confusing myself/everyone...oy. Here it goes:

The spreadsheet I'm using has several categories, each identified by a label in Column A that is made up of merged cells, and rotated 90 degrees. For a (hopefully) clarifying example, say I have categories "Pizza," "Pasta," and "Pie." The word "Pizza" is found sideways in Column A, merged over rows 1-5 which contain different types of pizza in Column B (B1 = Pepperoni, B2 = Cheese...you get the point, and I'm getting hungry). Below "Pizza" in Column A is another merged cell, merged over rows 6-10. This, of course, says "Pasta" and B6 = Linguini, B7 = Farfalle, B8 = Spaghetti...etc. Below "Pasta" is "Pie" over rows 11-15, and so on. Hopefully you get my point.

Now for my problem:

In order to update my spreadsheet (say someone just invented pineapple-guava-goatcheese-steak pizza and I need to add a row for it in the pizza category), I'd like to have two handy programs. First, I'd like to be able to unmerge the labels, and place the category name on each individual row of each section. This would mean that A1 = Pizza, B1 = Pepperoni; A2 = Pizza, B2 = Cheese...and so on. This way I can sort by category, but also by whatever the hey else I'd like to sort by.

Second, I'd like a program that will instantly re-do the beautiful merge job that my overlord requires, making all rows with the word "Pizza" in Column A merge together and turn 90 degrees.

I've never had to deal with merged cells in VB before, so I'm nervous and slightly frustrated. All attempts thus far have failed sadly. Any advice would be desperately welcome, and thank you very very very much in advance!

 

[raeannb]

Please? I'm desperate! I'll give you a virtual hug...

 

[raeannb]

If being more specific would be helpful, I'd love to figure out how to determine the value of of the cell in column A for each cell in B, even when A is still in merged form. I'm thinking some sort of If statement maybe? One idea:

New column is inserted between A and B
For all cells in C (formerly B, so C now contains Pepperoni, Cheese, etc)
If A (merged section) corresponding to evaluated row in C is "Pizza" Then
Column B, corresponding row = "Pizza"
Next cell
Delete Column A

Yes? Or would it be easier to have code that would maybe:

Unmerge A, Unrotate A
For all cells in A
If IsEmpty(Range("A" & i)) = True Then
Range("A" & i). Value = Range("A"&i-1).Value
Else: Next i
End if
Next i


Thoughts??

 

[Trevor G]

Can you copy part of the sheet as it is, then as you want it appear stage by stage.

Recording macros to merge and rotate text is a useful thing to look at if it helps.

You can either use Excel Jennie to upload HTML converted spreadsheets or just copy and paste.

 

[Norie]

How about showing this person how pretty a textbox or shape can be?

Also, why not <STRIKE>hide</STRIKE> store the data somewhere you can sort it as much and as often as you like.

Then use code to create your merged cells/textboxes/shapes?

 

[raeannb]

Sadly, I don't have admin rights on my computer so I can't download Excel Jeanie. Here's a copy/paste that I hope doesn't suck too much:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=100 rowSpan=5 width=64>Pizza</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Pepperoni</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Cheese</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Pineapple</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Sausage</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Mushroom</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=100 rowSpan=5>Pasta</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Linguini</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Farfalle</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Spaghetti</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Lasagna</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Rigatoni</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=100 rowSpan=5>Pie</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Plum</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Strawberry</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Blueberry</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Rhubarb</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Peach</TD></TR></TBODY></TABLE>

My table is essentially this, except "Pizza" "Pasta" and "Pie" are rotated 90 degrees (which I know how to do/undo already, so we can skip that detail and pretend they're horizontal). I've tried recording a macro to see what Excel does, but I'm worried that my spreadsheet is going to change a lot from month to month, and the code won't have the nice one-size-fits-all characteristic I'm hoping for. Help? Thank you!! You rock.

 

[raeannb]

How about showing this person how pretty a textbox or shape can be?

Also, why not <STRIKE>hide</STRIKE> store the data somewhere you can sort it as much and as often as you like.

Then use code to create your merged cells/textboxes/shapes?

Curious...I don't know much about hiding. Can I still sort my data if the merged cells are hidden? And if I add a row in one of the sections, can I get the merged cells to adjust their range accordingly? (I'm thinking some sort of Find function, maybe all rows with category pizza = range1 then set the merge area for column A to range1....or something?)

 

[Trevor G]

Take a look at this code.

If you select one of your merged fields in column A then run this code and say yes to the message and select 1 (as default) it will insert only a single row.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> newrow()<br><br><SPAN style="color:#00007F">Dim</SPAN> ANS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>ANS = MsgBox("Do you want to insert new rows here?", vbYesNo, "Insert Rows")<br><SPAN style="color:#00007F">If</SPAN> ANS = vbNo <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>x = InputBox("How many Rows do you want to insert?", "Number of Rows to Insert", "1")<br><SPAN style="color:#00007F">If</SPAN> x = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>z = ActiveCell.Row<br>ActiveSheet.Unprotect Password:="password"<br>ActiveCell.Resize(x).EntireRow.Insert<br>Range(Cells(z, 10), Cells(z + x - 1, 12)).MergeCells = <SPAN style="color:#00007F">True</SPAN><br>Range(Cells(z, 13), Cells(z + x - 1, 15)).MergeCells = <SPAN style="color:#00007F">True</SPAN><br>ActiveSheet.Protect Password:="password"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It came from this post

http://www.mrexcel.com/forum/showpost.php?p=413296&postcount=5

 

[Norie]

I really meant put the actual data on another worksheet and then run some code that produces the output you need.

For example if you had a worksheet called data with the data you posted but with pizza/pasta etc filled in on each row you could try this code.

Option Explicit
 
Sub CreatemMergedCells()
Dim wsData As Worksheet
Dim wsMerged As Worksheet
Dim rngProduct As Range
Dim rngDst As Range
Dim NoRows As Long
 
    Set wsData = Worksheets("Data")
 
    Set wsMerged = Worksheets("Sheet2")
 
    Set rngProduct = wsData.Range("A1")
 
    Set rngDst = wsMerged.Range("A1")
 
    While rngProduct.Value <> ""
 
        NoRows = Evaluate("=COUNTIF(Data!A:A,Data!" & rngProduct.Address & ")")
 
        rngProduct.Offset(, 1).Resize(NoRows).Copy rngDst.Offset(, 1)
 
        rngProduct.Copy rngDst
 
        With rngDst.Resize(NoRows)
 
           .HorizontalAlignment = xlCenter
           .VerticalAlignment = xlBottom
           .WrapText = False
           .Orientation = -90
           .AddIndent = False
           .IndentLevel = 0
           .ShrinkToFit = False
           .ReadingOrder = xlContext
           .MergeCells = True
        End With
 
        Set rngProduct = rngProduct.Offset(NoRows)
 
        Set rngDst = rngDst.Offset(1)
 
    Wend
 
End Sub

PS I tried posting a before and after but it did work.

Give it a try and you'll see the results - they aren't perfect but it might be a start.

 

[raeannb]

Thanks for your help, everyone! I decided to run with one of my earlier thoughts, and added in a few tips from all of you. Here's what worked:

'Count all rows (table starts in 6th row, with headers in 5th)
x = Range("C65536").End(xlUp).Row - 5

'Unmerge and unrotate labels in column A
With Columns("A")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

'Fill in labels
For i = 1 To x
If IsEmpty(Range("A" & i + 5)) = True Then
Range("A" & i + 5).Value = Range("A" & i + 4).Value
End If
Next i

It's perfect! Thank you all!!!