Forum Discussion

Bernadette Cochrane's avatar
Bernadette Cochrane
Copper Contributor
Jul 22, 2018

macro to add rows How enter realtive reference after row inserted?

 

 Dim MyRange As Range
    Dim Icounter As Long
      '    Set MyRange = Range("C4:D14")
    '   For Icounter = MyRange.Rows.Count To 2 Step -1

'

MyRange.Rows(Icounter).EntireRow.Insert

 

MyRange.Rows(Icounter).EntireRow.Insert


'Step5  Incrament the Counter Down

    Next Icounter

End Sub

Macros & VBA
Need Help

3 Replies

  • Man Fai Chan's avatar
    Man Fai Chan
    Iron Contributor
    Jul 22, 2018

    Suppose A1 to A10 contains 1 to 10. 

     

    Then, the statement

                 Range("A4").entirerow.insert

    will produce a new row as the 4th row. 

     

    The column A will become 1, 2, 3, "", 4, 5, 6, 7, 8, 9, 10

     

    Hope that this can help you.

    • Bernadette Cochrane's avatar
      Bernadette Cochrane
      Copper Contributor
      Jul 22, 2018

      I need to add macro code between line inserts or use a DO while.

      I am having trouble with copying info down the spreadsheet

       

      Range("A5").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[-3]C" Range("A6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[-3]C" Range("B6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[1]C" Range("C6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[1]C" Range("D6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[1]C" Range("E6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[-3]C" Range("F6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[1]C" Range("A5:F6").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("H6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=+R[1]C*-1" Range("I7").Select ActiveCell.FormulaR1C1 = "=+RC[-1]*-1" Range("I6").Select ActiveCell.FormulaR1C1 = "=+RC[-1]*-1" Range("I6:I7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("J6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("I6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = " " Range("I7").Select ActiveCell.FormulaR1C1 = " " Range("A10").Select End Sub

       

      Thanks

      • Bernadette Cochrane's avatar
        Bernadette Cochrane
        Copper Contributor
        Jul 22, 2018

        How do you modify Code for relative reference?    

        Range("B7").Copy Destination:=Range("B6")

        Can formula be used in the loop?

         

        Thanks

Resources