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