Excel VBA - 插入行和插入列宏

Excel VBA - Insert Row & Insert Column Macros

提问by amfrancis

I have a "Task Tracker" workbook that uses columns A:J to display and calculate task information (A:E are for user-entered data, F:J contain formulas that use the information in C:E and perform calculations. F:J are hidden cells in the user view.) The remainder of the columns display a heat map of where the tasks fall on a timeline, and whether or not they are running on time, are behind, or are complete.

我有一个“任务跟踪器”工作簿，它使用列 A:J 来显示和计算任务信息（A:E 用于用户输入的数据，F:J 包含使用 C:E 中的信息并执行计算的公式。F： J 是用户视图中的隐藏单元格。）其余列显示任务在时间线上的位置的热图，以及它们是否按时运行、落后或完成。

There are two buttons for users to use: one to insert a new row, and one to insert a new column. The InsertRow() macro inserts a row into the list, then copies the formulas down from the above row. The InsertColumn() macro locates the last used column in the worksheet and copies everything over from the column to the left of it.

有两个按钮供用户使用：一个用于插入新行，一个用于插入新列。InsertRow() 宏在列表中插入一行，然后从上一行向下复制公式。InsertColumn() 宏定位工作表中最后使用的列并将所有内容从该列复制到其左侧。

Originally, I had a macro for InsertRow using Range that copied from Column F (where the formulas start) to Column XFD. However, once I created the InsertColumn macro I realized that I cannot do InsertRow like that because InsertColumn needs to locate the last data-containing column in the worksheet and add a new one to the right...and if InsertRow gets run first, InsertColumn won't work because the value for lastColumn comes back as the index of column XFD.

最初，我有一个使用 Range 的 InsertRow 宏，它从 F 列（公式开始的地方）复制到 XFD 列。但是，一旦我创建了 InsertColumn 宏，我就意识到我不能像那样执行 InsertRow，因为 InsertColumn 需要找到工作表中最后一个包含数据的列并在右侧添加一个新列……如果先运行 InsertRow，则 InsertColumn将不起作用，因为 lastColumn 的值作为列 XFD 的索引返回。

What I am looking for help with:I need to locate the lastColumn value in my InsertRow macro, then use that value as part of the Range when the program executes the Copy/Paste portion of the code. I think that the problem I'm having has to do with the fact that the code I'm using to find the last column returns the index, and the Range function needs the name of the column.

我正在寻求帮助：我需要在 InsertRow 宏中找到 lastColumn 值，然后在程序执行代码的复制/粘贴部分时将该值用作范围的一部分。我认为我遇到的问题与我用来查找最后一列的代码返回索引有关，而 Range 函数需要列的名称。

Here is what I have for both macros:

这是我对两个宏的内容：

Sub InsertTaskRow()

' InsertTaskRow Macro
'
' This macro inserts a new row below whatever role the user currently has selected, then
' copies the formulas and formatting from above down to the new row

    Dim lastColumn As Long
    Dim currrentRow As Long
    Dim newRow As Long

    lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column

    currentRow = ActiveCell.Row
    newRow = currentRow + 1
    ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown

    Range("F" & currentRow & ":" & lastColumn & currentRow).Copy Destination:=Range("F" & newRow & ":" & currentRow & newRow)

End Sub


Sub InsertColumn()

' InsertColumn Macro
'
' This macro copies the formulas and formatting from the last data-containing column
' in the worksheet, and pastes it into the next column to the right.

    Dim lastColumn As Long

    lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column

    MsgBox lastColumn

    Columns(lastColumn).Copy Destination:=Columns(lastColumn + 1)

End Sub

回答by Rawrcasm

You can try changing your lastColumnoccurances to the following:

您可以尝试将出现次数更改lastColumn为以下内容：

lastColumn = ActiveSheet.Range("A1").End(xlToRight).Column

This will stretch your range to all used cells. I tried using the clCellTypeLastCell, but it was pulling further than necessary for no apparent reason; even after deleting the entire columns it claimed were applicable. Just an FYI, there is no issue with using indexes or column names when utilizing Range()- even interchangibly, they are both fully qualified.

这会将您的范围扩展到所有使用过的单元格。我尝试使用 clCellTypeLastCell，但它没有明显原因地拉得比必要的更远；即使在删除它声称适用的整个列之后。仅供参考，在使用时使用索引或列名没有问题Range()- 即使可以互换，它们都是完全限定的。