在表Excel VBA中插入新列

I'm running into some issues on a worksheet that I'm building. I want to insert a column at a specific location in a table and then set the header.

我正在构建一个我正在构建的工作表上的一些问题。我想在表中的特定位置插入一列,然后设置标题。

I searched around for a while, and found (some version of) the following code:

我搜索了一会儿,发现(某些版本)以下代码:

ActiveSheet.Columns(2).Insert

This works to insert the column, but I'm not sure how to change the header of the table after this.

这可以插入列,但我不知道如何在此之后更改表的标题。

Also, I previously got some advice here on adding columns to the end of a table and naming them.

此外,我之前在这里提出了一些建议,即在表的末尾添加列并命名它们。

The code for that is:

代码是:

Dim oSh As Worksheet
Set oSh = ActiveSheet
Dim oLc As ListColumn
Set oLc = oSh.ListObjects("PropTable").ListColumns.Add

oLc.Name = "XYZ"

I tried combining these two methods in various ways, but I'm not having any luck. Is there any way to modify the second block of code so that it inserts a column in a specific location, rather than just adding a column?

我尝试以各种方式结合这两种方法,但我没有运气。有没有办法修改第二个代码块,以便它在特定位置插入一列,而不是只添加一列?

Thanks.

-Sean

2 个解决方案

#1

  Dim Table As ListObject
  Set Table = Sheet1.ListObjects("Table1")
  Table.ListColumns.Add 2
  Table.HeaderRowRange(2) = "New header"

#2

It is possible to add a column to a table in a particular place and name it, using the same line of code.

可以使用相同的代码行将列添加到特定位置的表中并对其进行命名。

Table.ListColumns.Add(2).Name = "New Header"

This will add a column to the left of the second column in the table and name it New Header. You can make your code dynamic by adding a column to the left of one that you know the name of. This way, it is not necessary to specify the integer value of a new column's fixed location.

这将在表格的第二列左侧添加一列,并将其命名为New Header。您可以通过在知道其名称的列的左侧添加一列来使您的代码动态化。这样,没有必要指定新列的固定位置的整数值。

Dim newColNum as Integer
newColNum = Range("Table[Column Name]").Column
Table.ListColumns.Add(newColNum).Name = "New Header"

[Column Name] is the name of the column in your table where you want to insert a new column. It can have any position in the table, and you can pass it's value as an integer to the Add.

[列名]是表中要插入新列的列的名称。它可以在表中有任何位置,您可以将它的值作为整数传递给Add。

#1

  Dim Table As ListObject
  Set Table = Sheet1.ListObjects("Table1")
  Table.ListColumns.Add 2
  Table.HeaderRowRange(2) = "New header"

#2