Excel VBA：如何在范围中插入新行并复制公式 [英] Excel VBA: how to insert a new row into a range and copy formulas

问题描述

我有一个命名范围，如下所示：A2：D3

I have a named range like the following covering A2:D3

ITEM    PRICE   QTY SUBTOTAL
1           10  3   30
1           5   2   10
           TOTAL:   40

我正在寻找一些VBA代码将新行插入范围复制公式不值。

I am looking for some VBA code to insert a new row into the range copying the formulas not values.

任何提示/链接都非常感谢。

Any tips/links greatly appreciated.

推荐答案

应该这样做：

Private Sub newRow(Optional line As Integer = -1)
Dim target As Range
Dim cell As Range
Dim rowNr As Integer

    Set target = Range("A2:D3")

    If line <> -1 Then
        rowNr = line
    Else
        rowNr = target.Rows.Count
    End If

    target.Rows(rowNr + 1).Insert
    target.Rows(rowNr).Copy target.Rows(rowNr + 1)
    For Each cell In target.Rows(rowNr + 1).Cells
        If Left(cell.Formula, 1) <> "=" Then cell.Clear
    Next cell
End Sub

这篇关于Excel VBA：如何在范围中插入新行并复制公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！