Extending A Table Range

USAMax

Well-known Member
Joined
May 31, 2006
Messages
843
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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).
 
Upvote 0
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 ӿլ
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top