Shift Cells down without affecting row below

  • I am trying to utilize the Range.Insert method to insert new rows below my table in Range("G17:K17") in the "Output Sheet" worksheet of my attached file. The code in InsertRows function in Module1 is as follows.


    Code
    Set ws1 = ThisWorkbook.Worksheets("Output Sheet")
    Set rng1 = ws1.Range("G17:K17")
    rng1.Insert


    However, executing the above code affects row 22 in Output Sheet and it is no longer of contiguous color. Is it possible to shift cells down in the above range while keeping row 22 intact?


    File1.xlsm

  • Is this what you need ?

    Code
    Function InsertRows()
        Dim ws1    As Worksheet
        Dim rng1   As Range
        Set ws1 = ThisWorkbook.Worksheets("Output Sheet")
        Set rng1 = ws1.Range("G17:K17")
        rng1.Insert
        ws1.Range("G22:K22").Delete Shift:=xlUp '<= added
    End Function

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!