vba 添加新数据时 Excel 表不扩展
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19957206/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel table not expanding when new data is added
提问by user2988230
I have an Excel spreadsheet which contains four data tables on the same tab. The final 3 columns in each contain formulae.
我有一个 Excel 电子表格，其中在同一选项卡上包含四个数据表。每列中的最后 3 列包含公式。
I then have a vba code which performs an sql look up within an SQL Server table and returns a set number of columns to update each of the four tables.
然后我有一个 vba 代码，它在 SQL Server 表中执行 sql 查找并返回一组列数来更新四个表中的每一个。
For three of these tables, the data is pasted in and the table automatically expands and the formulae is copied to the end of the table.
对于其中三个表，数据被粘贴进来，表自动展开，公式被复制到表的末尾。
However, for the first table, the data is pasted in but the table does not expand and hence, the formulae does not get added to the rows outside of the table.
但是，对于第一个表，数据已粘贴到其中，但表不会扩展，因此，公式不会添加到表外的行中。
I have searched high and low for a fix but no joy. I have searched the data extraction VBA code with no luck (the code is an exact match) and no joy in the table properties. I have also checked the Options menu for Auto Format As You Type settings but all seems fine.
我一直在寻找解决办法，但没有任何乐趣。我在没有运气的情况下搜索了数据提取 VBA 代码（代码完全匹配）并且在表属性中没有任何乐趣。我还检查了“键入时自动格式化”设置的“选项”菜单，但一切似乎都很好。
Does anyone have any ideas for me?
有人对我有什么想法吗？
Thanks,
谢谢，
David.
大卫。
Hi All again,
大家好，
Would love to know if anyone has an answer for this but, in the mean time I have fudged the code to automatically adjust the size of the table based on the row number for the last cell of data.
很想知道是否有人对此有答案，但与此同时，我伪造了代码以根据最后一个数据单元格的行号自动调整表格的大小。
Thanks,
谢谢，
David.
大卫。
回答by Craig Hatmaker
The most common cause is due to differing number of, or mis-aligned columns (vertically stacked tables) or differing number of, or mis-aligned rows (horizontally stacked tables).
最常见的原因是由于不同数量的或未对齐的列（垂直堆叠的表）或不同数量的或未对齐的行（水平堆叠的表）。
For simplicity, let us use vertically stacked tables to illustrate what is happening. Consider an upper table with 2 columns and a lower table with 3. If we try to insert a row to the upper table, XL attempts to push all cells beneath the table down, but ONLY the cells directly beneath the table. So in this case cells below the 2 columns will be pushed down. XL quickly realizes that if it pushes just 2 columns down, the third column in the lower table won't be pushed down resulting in skewing the lower table. XL makes no attempt to figure out how to move the lower tables. It just refuses to even try.
为简单起见，让我们使用垂直堆叠的表格来说明发生了什么。考虑一个有 2 列的上表和一个有 3 列的下表。如果我们尝试在上表中插入一行，XL 会尝试将表下方的所有单元格向下推，但仅将表正下方的单元格推下。因此，在这种情况下，2 列下方的单元格将被向下推。XL 很快意识到，如果它只向下推动 2 列，则不会向下推动下方表格中的第三列，从而导致下方表格倾斜。XL 没有试图弄清楚如何移动较低的桌子。它只是拒绝尝试。
The same holds true for adding columns to tables on the left for which there are tables directly to the right. If adding columns to a table would result in skewing rows of a table to the right, XL refuses to help.
将列添加到左侧的表中也是如此，而右侧的表则直接为该表添加列。如果向表中添加列会导致表的行向右倾斜，XL 拒绝提供帮助。
To keep this from being a problem we can do a few things:
为了避免这成为一个问题，我们可以做一些事情：
- Keep the largest tables on top and to the left and align all table first columns and first rows.
- If only rows are added to tables, don't stack them vertically
- And if only columns are added to tables, don't stack them horizontally.
- 将最大的表格放在顶部和左侧，并将所有表格的第一列和第一行对齐。
- 如果只有行被添加到表中，不要垂直堆叠它们
- 如果只有列被添加到表中，不要水平堆叠它们。
回答by Israel Shavit
Herewith is an example of enforcing expansion of a table after data was copied over to the bottom of the table. This is done by resizing the table. Originally the table wasn't auto-resized as it has additional calculation columns. The first line of the code finds the last row of data and the second line states the range to which the table needs to be resized. The third line resizes the table to the new range. Replace sXXX and tXXX with your sheet name and table name.
以下是在将数据复制到表底部后强制扩展表的示例。这是通过调整表格大小来完成的。最初该表没有自动调整大小，因为它有额外的计算列。代码的第一行查找最后一行数据，第二行说明表需要调整大小的范围。第三行将表格调整为新范围。将 sXXX 和 tXXX 替换为您的工作表名称和表格名称。
Sub ResizeTableXXX()
myLastrowOfData = sXXX.Range("A1000000").End(xlUp).Row
myNewRangeSize = "$A:$P$" & myLastrowOfData
sXXX.ListObjects("tXXX").Resize Range(myNewRangeSize)
End Sub
HIH
HIH
回答by Joe
I ran into the same issue and it was driving me nuts. Found out that in a few columns I had dragged formulas down pretty far (they were IF statements to return nothing if the first column was empty) so I didn't see them.
我遇到了同样的问题，这让我发疯。发现在几列中我将公式拖得很远（它们是 IF 语句，如果第一列为空则不返回任何内容）所以我没有看到它们。
So remove any formulas that are 'under' the last row of the table. Should clear up the issue.
因此，删除表格最后一行“下方”的所有公式。应该把问题弄清楚。
