Extending A Table Range

USAMax

Well-known Member
Joined
May 31, 2006
Messages
826
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

Well-known Member
Joined
May 31, 2006
Messages
826
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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,166
Messages
5,467,014
Members
406,518
Latest member
SilverSixx

This Week's Hot Topics

Top