CODEWITHSUNDEEP
vbaexcel
wimle
wimle

Reputation: 25

Excel 2013 VBA Macro - EntireRow.Insert loop not working right

I am using Excel 2013 Home and Office. Does someone have any idea why these lines of code don't work as expected?

rSel = ActiveCell.Row

'Insert 10 Rows
For nCol = 1 To 10
      Cells(rSel + 1, 1).EntireRow.Insert
      nCol = nCol + 1
Next nCol

Simply put, the loop is supposed to insert 10 rows below the active cell but I end up with only 8. Even if I changed 10 to a higher number, I end up with 8 new rows only. I also tried a simpler, single line of code (below) but the result is the same.

Cells(rSel + 1, 1).EntireRow.Resize(10).Insert

Before anyone answers, I want to detail what this is for (in case that helps). The worksheet contains a header in Range A1:F5 and a footer in Range A8:F18. Data Entry starts at cell A6 from a list using Data Validation. The worksheet MUST BE DESIGNED as it is.

As the user picks choices from the list, a new row is inserted below that row. So, if I pick an item from the list at A6, a new row is inserted below that shifting the footer down. At A10 (5th selection), the code above is executed supposedly pushing the footer down 10 rows so that it appears on the 2nd page in its entirety and won't look weird when it's printed out. A set of code then formats and copies the header and pastes those into the new set of rows on the 2nd page above the footer. I will end up with 2 empty rows on page 1 after the 6th and final selection for that page.

Everything else works perfectly except the part when I want to insert 10 new rows because it consistently only gives me 8.

Upvotes: 2

Views: 1528

Answers (2)

jsotola
jsotola

Reputation: 2278

just use this command to insert 10 rows 

activecell.Resize(10).EntireRow.Insert              ' insert above ActiveCell

activecell.offset(1).Resize(10).EntireRow.Insert    ' insert below ActiveCell

Upvotes: 2

Alexander Bell
Alexander Bell

Reputation: 7918

In order to insert 10 rows below the ActiveCell use the following code snippet:

Sub InsertRow()
rSel = ActiveCell.Row
'Insert 10 Rows
For nCol = 1 To 10
      Cells(rSel + 1, 1).EntireRow.Insert
Next nCol
End Sub

(remove that line causing an error: nCol = nCol + 1)

For even better performance you can use the following code snippet inserting 10 rows at once:

Sub InsertRowBestPerformance()
    r = ActiveCell.Row
    'Insert 10 Rows below ActiveCell
    Rows(r + 1 & ":" & r + 10).EntireRow.Insert
End Sub

This could be written in a compact form with just a single line of code:

Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 10).EntireRow.Insert

Hope this will help.

Upvotes: 2

Related Questions