Inconsistent Formatting with Row Addition to ListObject

[NorthbyNorthwest]

Hi, everyone. I am working with a table (listobject). I notice the exact formatting sometimes fails to carry over when I add a row to the bottom of the table. Don't understand why this happens. I thought consistent formatting was a feature of tables. Anyway, I would like to add a bit a code following each row addition. I simply want the code to copy the row above the new addition and paste formatting to new row. I tried with code below. But it blows up. Can someone assist?

Sub LastRow()
Dim loLastRow As Long

With ActiveSheet.ListObjects("Details")
loLastRow = .DataBodyRange.Rows.Count + .HeaderRowRange.Row
'MsgBox loLastRow
.ListRows(loLastRow.Offset(-1, 0)).Range.Copy
.ListRows(loLastRow).Range.PasteSpecial Paste: xlFormats
End With

End Sub

 

[Alex Blakenburg]

Unless you intentionally have inconsistent formatting on your rows, using code to do this will create rather than solve your issue.

Tables (ListObjects) internally store the default formatting (and formula) to use and to apply to all rows.
I have not been able to find out where it stores it since you can delete all the rows but it will still come back.
If you use inconsistent formats or formulas within the same column the table can get confused and you will no longer get the results you are expecting.
To fix it you need to recopy the formatting or formula to all the rows in the table "at the same time".

So
• Clear all filters (optional just to play it safe)
• pick a cell that has the format you want to be the default formatting (or formula)
• Click on the Paint Brus
• Ctrl+<space bar> (selects all rows of the table databody for that column AND copies the formatting at the same time)

Test if the addtion of new rows now applies the correct formatting.

Let me know how you go.

 

[NorthbyNorthwest]

Hi, Alex. Thanks for responding and sharing the keyboard shortcut. It's much easier than using the mouse. I have three columns that are right aligned with an indent. When I add row I get the following. I have no problem fixing it. I just thought there might be another solution.

 

[Alex Blakenburg]

I have not used indent before in a table and just tried. The same principle applies and seems to work for me.

How are you getting the new data into the table ?
If you are using code can you show me the code ?

If not can you drop a copy a sample of your workbook on dropbox, google drive etc and post the link to it here.
Just make sure the problem still exists in the sample you provide,

 

[Alex Blakenburg]

One more question, are you using a custom number format ?
If you are select the whole column using Ctrl+<space bar>
change the format to something else eg "General"
then reapply your custom formatting.

 

[NorthbyNorthwest]

Data is being entered manually. So, there's no code. I added rows both using code and manually (tab key). Got the same result. Maybe it's the indent that's causing the problem. I think I'll continue fixing issue when it arises using the keyboard shortcut. Again, thanks for the assistance.

 

[Alex Blakenburg]

I wouldn't be happy with that.
You could try including the header when you reformat
ctrl+<spacebar> <spacebar> (spacebar twice will include the heading)
then apply your formatting
then put the heading cell formatting back to what it was.

You shouldn't need to do that but I have heard it sometimes fixes it.

If they are numbers try my post #5 first.

PS: Selecting the whole table column can be done by hovering over the top line of the heading cell until you get a solid black down arrow.
1 click select the databody range 2 clicks includes the heading.

 

[NorthbyNorthwest]

Hi, Alex. I'm not using a custom number format. I tried post #5. It worked in that it aligned the three number columns like rest of table. However, when I added a new row, it misaligned again. Finally, I decided to reformat the entire table. That worked. It is now formatting row additions correctly. Thanks again for the assist.

 

[Alex Blakenburg]

Glad that you got it sorted. Would not have considered having to change the format each time you add a row acceptable.
I would be interested to see the steps you took that fixed it for you ie when you reformatted the entire table (and did you include the heading row).
You could then mark that post as a solution.