Extending A Table Range

[USAMax]

This should be so simple that I am embarrassed to ask for help. I imported my data and I have a macro that updates the data. Nothing new until new items were added, creating new rows at the bottom and now the Table Format does not include these rows. There are so many formats available that I don’t want to guess which one I used when I created it I just want to extend the data range.

I do not care if this is done through the macro or right on the sheet as this does not happen that often. This should be simple, can somebody help?

 

[Asala42]

The quick & easy approach is to just use the Format Painter.

- Highlight a properly formatted row
- Hit the format painter button (usually near copy/paste - looks like a paintbrush)
- Select the new rows. This will copy the format over. (Shift+End then Down arrow usually will do this).

 

[USAMax]

Thank you for you feedback and this is an option that I have considered but my macro colors the text Green for new data, Red for anything that needs attention and a Gray background when the item is not available. Copying the format would alter the data and I hope to avoid this. Also, at the bottom of the Table Format is a blue double line that I would like to continue to represent the end of my data.

Is there another option?

Dave ӿլ

 

[Asala42]

If it were me, I'd probably toss in a bit of format code immediately prior to the coding that alters colors for the new records. Something like:

dim endrow1 as long
endrow1 = Range("A1048576").End(xlUp).Row

Range("A2:Z2").Copy  'this row holds the desired base formatting
Range(Cells(3, 1), Cells(Endrow1, 26)).PasteSpecial (xlPasteFormats)

'double underline last row
Range(Cells(Endrow1, 1), Cells(Endrow1, 26)).Borders(xlEdgeBottom).LineStyle = xlDouble

'enter your normal code here.