JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,537
- Office Version
- 365
- Platform
- Windows
I am working on some macro code that will process a range of data. It will actually need access to two separate, but "connected" ranges. I would love to make them both tables so I can take advantage of the table features, but I don't see how to make that work.
Here's a simple version of my data:
The data in rows 2-5 must align with the columns in the table. The problem is how to keep them aligned if I need to add, delete, or relocate and columns.
When I have just one table, I always add rows by right-clicking on the column header and selecting Insert. That preserves the size of the other columns. If I do it by right-clicking inside the table, any columns to the right get messed up. This is what happens it I right-click in D7 and select Insert Column to the Left.
But if I make B2:G5 into a table, Excel will no longer allow me to insert from the column header. I can only do it from inside the tables and then I have to make sure I do it exactly the same in both tables to keep them aligned.
I guess my current plan is to leave the top rows as a range (not a table) so I can insert and delete from the column headers. That should keep everything aligned.
Are there any better ways?
Here's a simple version of my data:
Weighted Ratings.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | |||
2 | Type | Lo | Hi | Hi | Hi | |||
3 | Max | $30.00 | ||||||
4 | Min | 4.2 | 200 | |||||
5 | Weight | 1 | 1 | 1 | 2 | |||
6 | ||||||||
7 | Item | Price | Rtg | #Rev | Capacity | Comments | ||
8 | D | $22.99 | 4.4 | 595 | 25 lb | |||
9 | I | $49.50 | 4.5 | 826 | 30 lb | |||
10 | A | $14.17 | 4.6 | 10,904 | 40 lb | |||
11 | G | $19.95 | 4.6 | 159 | 40 lb | |||
12 | C | $54.98 | 4.7 | 504 | 50 lb | |||
13 | E | $23.95 | 4.7 | 2,095 | 45 lb | |||
14 | F | $7.99 | 4.7 | 53 | 50 lb | |||
15 | H | $31.97 | 4.7 | 476 | 40 lb | |||
16 | B | $13.12 | 4.8 | 10,583 | 55 lb | |||
Simple |
The data in rows 2-5 must align with the columns in the table. The problem is how to keep them aligned if I need to add, delete, or relocate and columns.
When I have just one table, I always add rows by right-clicking on the column header and selecting Insert. That preserves the size of the other columns. If I do it by right-clicking inside the table, any columns to the right get messed up. This is what happens it I right-click in D7 and select Insert Column to the Left.
Weighted Ratings.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
7 | Item | Price | Column1 | Rtg | #Rev | Capacity | Comments | ||
8 | D | $22.99 | 4.4 | 595 | 25 lb | ||||
9 | I | $49.50 | 4.5 | 826 | 30 lb | ||||
10 | A | $14.17 | 4.6 | 10,904 | 40 lb | ||||
11 | G | $19.95 | 4.6 | 159 | 40 lb | ||||
12 | C | $54.98 | 4.7 | 504 | 50 lb | ||||
13 | E | $23.95 | 4.7 | 2,095 | 45 lb | ||||
14 | F | $7.99 | 4.7 | 53 | 50 lb | ||||
15 | H | $31.97 | 4.7 | 476 | 40 lb | ||||
16 | B | $13.12 | 4.8 | 10,583 | 55 lb | ||||
Simple |
But if I make B2:G5 into a table, Excel will no longer allow me to insert from the column header. I can only do it from inside the tables and then I have to make sure I do it exactly the same in both tables to keep them aligned.
I guess my current plan is to leave the top rows as a range (not a table) so I can insert and delete from the column headers. That should keep everything aligned.
Are there any better ways?