CODEWITHSUNDEEP
excelvba
user3089580
user3089580

Reputation:

insert blank first row

i am using below vba formula to inset new row keeping formula as it is in A1 row. 

Sub move_and_delete_rows()
Dim i As Long
    For i = 2 To Worksheets.Count
        With Worksheets(i)
            With .Range("A1")
                .Offset(1, 0).EntireRow.Insert
                .CurrentRegion.Copy
            End With
            .Range("A2").PasteSpecial Paste:=xlPasteValues
            .Range("A21").EntireRow.Delete
        End With
    Next i
End Sub

above formula working fine. but that it should insert row say from column A to column J. after J column no insertion of row.

Upvotes: 0

Views: 165

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

1) If you want to insert the whole row but copy only 10 cells from A1 to J1 then instead of this line:

.CurrentRegion.Copy

try this line:

.Resize(1,10).Copy

2) But if you want to insert only 10 cells below range A1:j1 then instead of this line:

.Offset(1, 0).EntireRow.Insert

try this line:

.Offset(1,0).Resize(1,10).Insert

You can combine both options if you want to copy and insert only 10 cells.

Upvotes: 1

Related Questions