excel - todas - Cómo insertar una nueva fila en un rango y copiar fórmulas

no puedo arrastrar formulas en excel

Question

Tengo un rango con nombre como el siguiente que cubre A2: D3

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

Debo insertar una nueva fila usando VBA en el rango copiando las fórmulas, no los valores.

Cualquier sugerencia / enlace muy apreciado.

Answer 1

Si comienza a grabar una macro y realmente hace la tarea en la mano, generará el código para usted. Una vez que haya terminado, deje de grabar la macro y tendrá el código necesario, que luego podrá modificar.

Answer 2

Esto debería ayudarlo: http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Answer 3

Esto debería hacerlo:

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

Answer 4

Necesitaba rodar una solución que funcionara de la misma manera en que una consulta de conexión de datos amplía un rango de resultados con fórmulas de autocompletar a la derecha. Tal vez dos años tarde para la recompensa, pero estoy feliz de compartir de todos modos!

Public Sub RangeExpand(rangeToExpand As Range, expandAfterLine As Integer, Optional linesToInsert As Integer = 1, Optional stuffOnTheRight As Boolean = False)
Debug.Assert rangeToExpand.Rows.Count > 1
Debug.Assert expandAfterLine < rangeToExpand.Rows.Count
Debug.Assert expandAfterLine > 0
If linesToInsert = 0 Then Exit Sub
Debug.Assert linesToInsert > 0
Do
rangeToExpand.EntireRow(expandAfterLine + 1).Insert
linesToInsert = linesToInsert - 1
Loop Until linesToInsert <= 0
If stuffOnTheRight Then
rangeToExpand.Item(expandAfterLine, rangeToExpand.Columns.Count + 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(rangeToExpand.Item(expandAfterLine, 1), Selection).Select
Else
Range(rangeToExpand.Item(expandAfterLine, 1), rangeToExpand.Item(expandAfterLine, rangeToExpand.Columns.Count)).Select
End If
Selection.AutoFill Destination:=Range(rangeToExpand.Item(expandAfterLine, 1), rangeToExpand.Item(rangeToExpand.Rows.Count, Selection.Columns.Count))
End Sub

Answer 5

Esta respuesta aborda los siguientes 3 problemas con la respuesta actualmente aceptada de @marg publicada originalmente el 13 de abril de 2010 a las 9:43.

1. target.Rows(rowNr + 1).Insert : 1.1. no extiende el rango designado por una fila (AFAIK es la única forma de hacerlo implícitamente mediante Insertar fila (frente a la modificación explícita de la definición de rango) y hacerlo después de que el número de fila especificado se encuentre en la fila # 1 a la cuenta - 1) y 1.2) solo cambia Columnas en el rango target hacia abajo una fila. En muchos casos (y probablemente en la mayoría), las columnas a la derecha y / o izquierda del rango target deben desplazarse hacia abajo.

2. target.Rows(rowNr).Copy target.Rows(rowNr + 1) no copia los formatos que a menudo también se desean normalmente.

Private Sub InsertNewRowInRange (_ TargetRange As Range, _ Opcional InsertAfterRowNumber As Integer = -1, _ Optional InsertEntireSheetRow As Boolean = True)

'' -- InsertAfterRowNumber must be 1 to TargetRange.Rows.Count - 1 for TargetRange to be extended by one Row and for there to be
'' -- Formats and Formulas to copy from (e.g. can''t be 0). Default: If -1, defaults to TargetRange.Rows.Count.
'' -- Recommend dummy spacer Row at the bottom of TargetRange which, btw, would also be necessary to manually extend a Range
'' -- by one Row implicitly via Insert Row (vs. explicilty via changing Range definition).
If InsertAfterRowNumber = -1 Then
InsertAfterRowNumber = TargetRange.Rows.Count
End If
If InsertEntireSheetRow Then
TargetRange.Cells(InsertAfterRowNumber + 1, 1).Select
Selection.EntireRow.Insert
Else
TargetRange.Rows(InsertAfterRowNumber + 1).Insert
End If
TargetRange.Rows(InsertAfterRowNumber).Select
Selection.Copy
TargetRange.Rows(InsertAfterRowNumber + 1).Select
Selection.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Selection.PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End Sub