英文:
Insert column into table while also shifting all adjacent cells VBA Excel
问题
我在我的Excel工作表中有一个包含可变行数的表格。偶尔需要在表格右侧添加新列。
我已经在VBA中完成了这个操作，使用以下代码：
tbl.ListColumns.Add().Name = temp
其中temp是我要创建的新列的标题名称。
现在的问题是，表格右侧（不属于表格的部分）有一列已填充的单元格。当我向表格添加新列时，只有与表格直接相邻的单元格向右移动。相邻列中的其他所有内容保持在其原始位置。
是否有一种程序化地将新列插入到表格右侧，并同时将与表格相邻的整个列的内容向右移的方法？
我已经尝试使用Columns(x).Insert shift:=xlShiftRight，但除非我使用不当，否则它只会在工作表中添加新列，我仍然需要在表格中添加新列，这会导致相同的问题。
英文:
I have a table in my excel sheet with a changing number of rows. On occasion a new column may need to be added to the table at the right.
I have accomplished this in VBA using
tbl.ListColumns.Add().Name = temp
where temp is the header name of the new column I am creating.
Now, the issue is, to the right of the table (not a part of the table), I have a column of populated cells. When I add a new column to the table, only the cells directly adjacent to the table shift to the right. Everything else in the adjacent column stays in its original position.
Is there a way to programmatically insert a new column into the right side table while also shifting the contents of the entire column adjacent to the table as well?
I have tried using Columns(x).Insert shift:=xlShiftRight but unless I'm using it wrong it only adds a new column to the sheet and I still have to add a new column to the table which causes the same issue.
答案1
得分: 3
不使用ListColumns.Add()，您可以简单地在表格旁边的单元格中写入内容。当列为空时（确切地说，只有表格旁边的单元格需要为空），Excel会自动为表格创建新列。但是，不会进行移动操作，剩余表格的结构保持不变。
现在如果我理解您正确的话，表格旁边已经有数据，所以您需要首先插入一个新的（Excel）列。
以下例程将完全做到这一点：搜索表格旁边的第一个单元格，插入一列，并在该列的标题行中写入内容。
Sub CreateNewTableColumn(table As ListObject, caption As String)
Dim cell As Range
' 设置单元格为表格旁边的第一个单元格
Set cell = table.ListColumns(table.ListColumns.Count).Range.Offset(, 1).Resize(1, 1)
' 在该位置的工作表上添加一列。
cell.EntireColumn.Insert xlToRight
' 由于添加列会移动单元格引用，因此将其向后移动一列
Set cell = cell.Offset(0, -1)
' 只需写入标题，这将自动创建新列
cell.Value = caption
End Sub
假设tbl保存了对您的表格的引用，temp保存了新列的标题，您可以这样调用它：
CreateNewTableColumn tbl, temp
英文:
Instead of using ListColumns.Add(), you can simply write something into a cell that is next to the table. When the column is empty (to be precise, only the cells next to the table need to be empty), Excel will automatically create a new column for the table. However, no shifting is done and the structure of the remaining sheet stays intact.
Now if I understand you correctly, there is already data next to the table, so you will need to insert a new (Excel-)column first.
The following routine will do exactly that: Search the first cell next to the table, insert a column and write something into the header row of that column.
Sub CreateNewTableColumn(table As ListObject, caption As String)
Dim cell As Range
' Set Cell to first cell next to the table
Set cell = table.ListColumns(table.ListColumns.Count).Range.Offset(, 1).Resize(1, 1)
' Add an column to the sheet at that position.
cell.EntireColumn.Insert xlToRight
' As adding a column will move the cell reference, to back one column
Set cell = cell.Offset(0, -1)
' Just write the Caption, this will automatically create a new column
cell.Value = caption
End Sub
Assuming that tbl holds a reference to your table and temp holds the caption for the new column, you can call it with
CreateNewTableColumn tbl, temp
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论