how to add multiple rows in excel using vba

With the third row, G9, it would have shifted down by two rows and hence the new row will be inserted in row 11. The reason why Excel doesn't insert the new rows in the nominated row references is because it initially selects the specified cells and then inserts the first row in row 4, then moves to the next cell's reference which has shifted down by one row and inserts a new row in row 8. You can replace the range reference with, for example, ('B4,E7,G9') which will insert new rows in rows 4, 8 and 11. The row selection doesn't need to be in a single range. Rows Selection: Select where you want to insert new rows by changing the row number references ('B2:B4').

Application.Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets. You can also use the same method to copy a row and then insert it somewhere else. Now, what happens, when you insert a row before the 7 th row that new row becomes the 7 th row, and then the second line of code clears the formats from that row. When you run the above code, it inserts a new row before the 7 th row.

And the simplest way to deal with this thing is to use clear formats. When you insert a row where the above row has some specific formatting, in that case, the row will also have that formatting automatically. When you run this macro, it takes count of rows from the cell A1 and the row where you want to add rows from the cell B1. If you want to insert rows based on a cell value, then you can use the following code. It uses a FOR LOOP (For Next) to loop that number times and insert rows one by one. When you run this code, it asks you to enter the number of rows that you want to add and then the row number where you want to add all those rows. (Prompt:='After which row you want to add new rows? (Enter the row number') ICount = InputBox(Prompt:='How many rows you want to add?') To be honest, I haven’t found this method quite useful because you need to change the range if you want to change the count of the rows.

How to add multiple rows in excel using vba