如何在范围中插入新行并复制公式 [英] 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 to insert a new row using VBA 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

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