CODEWITHSUNDEEP
vbaexcel
Zorro
Zorro

Reputation: 5

Inserting blank rows after specific rows - VBA

This is a code I came up with until now

Sub RunMe()
Dim x As Integer
Dim sv As Integer

x = 11
sv = Range("MyTable").Rows.Count


Do
    Rows(x).Resize(sv).Insert
    x = x + sv
Loop Until IsEmpty(Cells(x, "A"))
End Sub

So basically this is supposed to insert blank cells in row 11. But the problem is it just adds blank cells at the end of the table instead after row 11. Is there any way to fix this? I am a pure beginner at VBA, this is my first time experimenting with it. Any help will be appreciated.

Upvotes: 0

Views: 5916

Answers (2)

Xabier
Xabier

Reputation: 7735

This could be achieved by:

LastRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
Sheet2.Range("A1:K" & LastRow).Copy 'amend to include the number of columns, also change 1 to 2 if using header and don't want to include them
Sheet1.Rows("11:11").Insert Shift:=xlDown

This will not create empty rows, but instead it will insert all the rows with data into Sheet1 Row 11.

So count the number of rows on Sheet2, then select the range to be copied, and finally inserting into row 11 of Sheet1.

UPDATED:

Sheet2.Range("MyTable").Copy
Sheet1.Rows("11:11").Insert Shift:=xlDown

Upvotes: 2

Vityata
Vityata

Reputation: 43595

Try this, for me it works quite ok:

Sub RunMe()

    Dim x As Integer
    Dim sv As Integer

    x = 11
    sv = Range("MyTable").Rows.Count + 1 
    Rows(x).Resize(sv).Insert

End Sub

If the table is with 23 rows, it inserts 23 empty rows after the 10. row.

Upvotes: 1

Related Questions