Anyone have code to properly insert a column when some of th

Brian

Board Regular
Joined
Apr 24, 2002
Messages
113
Anyone have code to properly insert a column when some of the cells are merged?

ie.
- Record which merged cells cross the column(s) to insert. (Perhaps simply record all merged cells on the sheet.)
- Unmerge all affected cells.
- Insert the column(s).
- Re-merge the affected cells, increasing the range of each merge to include the added columns. (I only need the macro to accomodate merges accross columns of a single row.)

This has always annoyed me in Excel 97, but I have yet to take the time to create the above code.

Alternatively, Is there a way to "Left Justify Accorss Section"? ie. I want my text to be able to wrap text, left justify accross several columns. This can be done with merged cells, but becaomes a pain whenever I want to add or a column.

Also, autofit row height does not work properly accross merged cells so I usually add a hidden column of approriate width that is set equal to the merged cells. (A waste of space, but if there is a better way, please let me know.)

WHY I ASK: I have a sheet to tracking tasks. The schedule dates work best as a horizontal table of narrow cells near the top of the page. The Detailed instructions and reference material are long paragraphs that are best in wide cell (thus the merged cells).

Thanks,

Brian

Files can be mailed to BW-NoSPAM1@earthlink.net

Example in html:

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=9><FONT COLOR=WHITE>Microsoft Excel - HeyBorisSuggestions-BrianWest6.xls______________Running: xl97 : OS = Windows (32-bit) 5.01</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=9>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>B20</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=6 BGCOLOR=White>Add columns for "date of last transaction" (or other status field) and "last price paid" (&/or standard cost). Place the description column after the price column so that it is easier to cut & paste p/n & desc. into the ECO form.</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>F</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>G</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>H</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=TOP ><A HREF=javascript:alert('=HYPERLINK(%22[%22&SUBSTITUTE(LEFT(CELL(%22filename%22,A1),SEARCH(%22]%22,CELL(%22filename%22,A1))),%22[%22,%22%22,1)&%22index!A%22%20&%20MATCH($A$2,Index!$B:$B,0),%22{%20INDEX%20}%22)')><FONT FACE=Arial COLOR=#0000FF>{ INDEX }</FONT></A></TD><TD COLSPAN=3 BGCOLOR=#CCFFFF ALIGN=Right VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Hey Boris! Suggestion by </FONT></TD><TD COLSPAN=4 BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Brian West</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><A HREF=javascript:alert('=IF(NOT(ISERROR(VLOOKUP(VALUE(RIGHT(CELL(%22filename%22,$A$2),LEN(CELL(%22filename%22,$A$2))-SEARCH(%22]%22,CELL(%22filename%22,$A$2),1))),Index_Table,COLUMN(Index!$J$1),0))),VALUE(RIGHT(CELL(%22filename%22,$A$2),LEN(CELL(%22filename%22,$A$2))-SEARCH(%22]%22,CELL(%22filename%22,$A$2),1))),RIGHT(CELL(%22filename%22,$A$2),LEN(CELL(%22filename%22,$A$2))-SEARCH(%22]%22,CELL(%22filename%22,$A$2),1)))')><FONT FACE=Arial COLOR=#0000FF>1.02</FONT></A></TD><TD COLSPAN=7 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Automation for CPSXREF to replace manual manipulation:</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Response:</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Details:</FONT></TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Original Submission</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Hey Boris! Submission</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Emails Sent</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Rejected</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Accepted </FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Implemented</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2002-05-01</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2002-05-02</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Priority:</FONT></TD><TD COLSPAN=6 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Low</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Resources:</FONT></TD><TD COLSPAN=6 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Low; $1000; 8 hour IT; 8 hours Eng</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Dependencies:</FONT></TD><TD COLSPAN=6 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Purchase of xxx.</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Email To</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Email Sent</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Response</FONT></TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Groups Affected:</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Charlie Brown</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2002-05-15</FONT></TD><TD COLSPAN=4 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Great</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Notification:</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Brian West</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2002-05-16</FONT></TD><TD COLSPAN=4 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Sucks</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>13</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Implementation by:</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Rudolph</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2002-05-17</FONT></TD><TD COLSPAN=4 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>But what if it snows?</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>14</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Approval by:</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Santa Clause</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2002-05-18</FONT></TD><TD COLSPAN=4 BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>15</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>1.00</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>BACKGROUND:</FONT></TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>16</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><A HREF=javascript:alert('=OFFSET($A16,-1,0)+0.01')><FONT FACE=Arial COLOR=#0000FF>1.01</FONT></A></TD><TD COLSPAN=7 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Blah Blah, Blah</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>17</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>2.00</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>PROBLEM:</FONT></TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>18</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><A HREF=javascript:alert('=OFFSET($A18,-1,0)+0.01')><FONT FACE=Arial COLOR=#0000FF>2.01</FONT></A></TD><TD COLSPAN=7 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>When looking for a part for something, I start by searching CPSXREF to determine if we already have anything suitable. If I find something promising, then I need to open ASK and run LI.100 to determine if we still buy this part and what the price is.</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>19</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>3.00</FONT></TD><TD BGCOLOR=#CCFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>SOLUTION:</FONT></TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD><TD BGCOLOR=#CCFFFF>
</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>20</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=TOP ><A HREF=javascript:alert('=OFFSET($A20,-1,0)+0.01')><FONT FACE=Arial COLOR=#0000FF>3.01</FONT></A></TD><TD COLSPAN=7 BGCOLOR=#FFFFFF ALIGN=Left VALIGN=TOP ><FONT FACE=Arial COLOR=#000000>Add columns for "date of last transaction" (or other status field) and "last price paid" (&/or standard cost). Place the description column after the price column so that it is easier to cut & paste p/n & desc. into the ECO form.</FONT></TD></TR><TR><TD COLSPAN=9><U>1.02</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by[HtmlMaker V1.25]</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT>
</CENTER>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Anyone have code to properly insert a column when some of the cells are merged?

ie.
- Record which merged cells cross the column(s) to insert. (Perhaps simply record all merged cells on the sheet.)
- Unmerge all affected cells.
- Insert the column(s).
- Re-merge the affected cells, increasing the range of each merge to include the added columns. (I only need the macro to accomodate merges accross columns of a single row.)

This has always annoyed me in Excel 97, but I have yet to take the time to create the above code.

Hi Brian,

Here is a sample code I made.

<pre>
Option Explicit
Sub InsertColumns() 'Just sample
Dim rngSelected As Range, lngCnt As Long, rngArr() As Range, i As Long
Set rngSelected = Selection
With rngSelected
If .Areas(.Areas.Count).Rows.Count <> Rows.Count Then Exit Sub
For lngCnt = 1 To .Areas.Count - 1
If .Areas(lngCnt).Columns.Count > 1 Then
ReDim Preserve rngArr(0 To i)
Set rngArr(i) = .Areas(lngCnt)
Debug.Print rngArr(i).Address
.Areas(lngCnt).MergeCells = False
i = i + 1
End If
Next
Columns(.Areas(.Areas.Count).Address).Insert Shift:=xlToRight
For lngCnt = LBound(rngArr) To UBound(rngArr)
rngArr(lngCnt).Resize(, rngArr(lngCnt).Columns.Count).MergeCells = True
Next
End With
Erase rngArr
End Sub
</pre>

HTH
 
Upvote 0
Hi me again. I think, maybe you need to "delete" columns too.

Please copy this into a standard module.
And run Sub AddRightClickMenu(). "MyInsert" and "MyDelete" menu will apper on your Right Click Menu.
If you don't need the menu, please run Sub Reset().

<pre>

Sub AddRightClickMenu()
With Application.CommandBars("Column")
.Reset
With .Controls.Add _
(Type:=msoControlButton, temporary:=False)
.Caption = "MyInsert"
.OnAction = "InsertColumns"
End With
With .Controls.Add _
(Type:=msoControlButton, temporary:=False)
.Caption = "MyDelete"
.OnAction = "DeleteColumns"
End With
End With
End Sub

Sub Reset()
Application.CommandBars("Column").Reset
End Sub

Private Sub InsertColumns()
Call LoopLine(True)
End Sub

Private Sub DeleteColumns()
Call LoopLine(False)
End Sub

Private Sub LoopLine(ByVal blnInsorDel As Boolean)
Dim rngSelected As Range, lngCnt As Long, rngArr() As Range, i As Long
Set rngSelected = Selection
With rngSelected
If .Areas(.Areas.Count).Rows.Count <> Rows.Count Then Exit Sub
For lngCnt = 1 To .Areas.Count - 1
If .Areas(lngCnt).Columns.Count > 1 Then
ReDim Preserve rngArr(0 To i)
Set rngArr(i) = .Areas(lngCnt)
Debug.Print rngArr(i).Address
.Areas(lngCnt).MergeCells = False
i = i + 1
End If
Next
If blnInsorDel Then
If .Areas.Count = 1 Then .Insert Shift:=xlToRight: Exit Sub
Columns(.Areas(.Areas.Count).Address).Insert Shift:=xlToRight
Else
If .Areas.Count = 1 Then .Delete Shift:=xlToRight: Exit Sub
Columns(.Areas(.Areas.Count).Address).Delete Shift:=xlToRight
End If
For lngCnt = LBound(rngArr) To UBound(rngArr)
rngArr(lngCnt).Resize(, rngArr(lngCnt).Columns.Count).MergeCells = True
Next
End With
Set rngSelected = Nothing
Erase rngArr
End Sub

</pre>

HTH
 
Upvote 0

Forum statistics

Threads
1,215,379
Messages
6,124,609
Members
449,174
Latest member
ExcelfromGermany

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
Back
Top