Working with Merged Cells

raeannb

Board Regular
Joined
Jun 21, 2011
Messages
86
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

raeannb

Board Regular
Joined
Jun 21, 2011
Messages
86
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 
Last edited:

raeannb

Board Regular
Joined
Jun 21, 2011
Messages
86
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

Board Regular
Joined
Jun 21, 2011
Messages
86

ADVERTISEMENT

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

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
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

Board Regular
Joined
Jun 21, 2011
Messages
86
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,352
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top