Auto Expanding table has inconsistant format?

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,351
Messages
5,528,192
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top