Auto Expanding table has inconsistant format?

[repairman615]

Hello Everybody,

The auto expand tables' new row format does not match the rest of the table.

Using VBA to autoexpand a table by finding the row below the table and making an entry. There is headers and a total row.

This happens also when manually turning off the total row, and then typing below the table to trigger the auto expand

<TABLE style="WIDTH: 987pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1314><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8118" width=222><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" span=2 width=100><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" span=2 width=79><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 35pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; HEIGHT: 15.75pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78 height=21 width=46>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78 width=89>466</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 71pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl79 width=94>3/7/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78 width=89>419</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 167pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl80 width=222>Pam</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 98pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl81 width=131>$ 2,076.35 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 75pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl79 width=100>3/15/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 75pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl79 width=100></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 98pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl81 width=131></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl82 width=89 align=right>(2,076.35)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78 width=79>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78 width=79></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 49pt; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #eaf1dd; COLOR: blue; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: underline; text-underline-style: single; text-line-through: none; mso-pattern: black none" class=xl83 width=65 align=right>466</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; HEIGHT: 15.75pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78 height=21>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78>467</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl79>3/11/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78>420</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl80>Joe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl81>$ 1,825.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl79>3/19/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl79></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl81></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl82 align=right>(2,125.00)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl78></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: 'Bookman Old Style'; BACKGROUND: #d7e4bc; COLOR: blue; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: underline; text-underline-style: single; text-line-through: none; mso-pattern: black none" class=xl83 align=right>467</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; HEIGHT: 15.75pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77 height=21 align=right>22</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77 align=right>456</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl86 align=right>Mar-4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77 align=right>123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77>sam</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77 align=right>12.45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl85 align=right>18-Mar</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl85 align=right>4-Mar</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77 align=right>55.45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77 align=right>-20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77>not same</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #eaf1dd; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl77>not same</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #eaf1dd; COLOR: blue; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: underline; text-underline-style: single; text-line-through: none; mso-pattern: black none" class=xl84 align=right>456</TD></TR></TBODY></TABLE>


The last row above is of the wrong format.

Maybe I could use a line of code to fill a format down if anyone knows of such, but before that I was wondering if there was some kind of option I am missing.

This has not always happened.

Any advice is greatly appreciated.

Thanks,
Jeff

 

[repairman615]

This does seem to work to copy the formats of the first row, and paste over the new row.

<font face=Courier New>        Range("A16:M16").Copy<br>        Range("A" & Rows.Count).End(xlUp).PasteSpecial Paste:=xlPasteFormats, _<br>        Operation:=xlNone, _<br>        SkipBlanks:=False, Transpose:=False</FONT>



Not natural yet it does seem to work, I am still unsure why the new row would be of a different format.... Even the cells below have been formatted to the same as desired. Maybe this has something to do with the total row. The total row was a recent addition to the table..maybe thats got something to do with it.