Excel VBA：如何在范围中插入新行并复制公式

本文介绍了Excel VBA：如何在范围中插入新行并复制公式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个命名范围，如下所示：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