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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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,095,183
Messages
5,442,866
Members
405,206
Latest member
warviksam

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top