macro or formula to insert row or rows at a certain point

October 9, 2019 at 02:10:49
Specs: Windows 7
How to insert rows in excel using formula:

I have an xls sheet having 2 columns continuously filled with data.

I need to insert 6 blank rows after each row of data using formula or macro or VB


See More: macro or formula to insert row or rows at a certain point

Reply ↓  Report •

#1
October 9, 2019 at 08:18:58
You didn't tell us which column(s) contain the data, so the following VBA code (macro) assumes Column A.

I not quite sure what you mean by "continuously filled with data", but I want to point out 1 possible issue:

If inserting 6 rows after each piece of data pushes the existing data off the bottom of the page, the code will fail. A worksheet allows for a maximum of 1,048,576 rows and you are asking for 7 rows per piece of data. Therefore, if you have more than 149,797 pieces of data, the code will fail because you will eventually exceed the number of allowable Rows.

That said, give this a try:

Sub InsertSixRows()

'Disable Screen Upadating so code runs faster
    Application.ScreenUpdating = False

'Determine last Row with data in Column 1 (A)
    lastRw = Cells(Rows.Count, 1).End(xlUp).Row

'Start at bottom of sheet and insert Rows
   For rw = lastRw To 1 Step -1
      Rows(rw & ":" & rw + 5).EntireRow.Insert
   Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 10, 2019 at 00:29:01
Thanks DerbyDad03;

My apology for not being able to clearly mentioning the details.

Data columns are B & C and by continuously filled means its limited to only 60 number of rows and NOT to the maximum.

I appreciate your reply and the solution. I will try this and will let you know. Thanks again.


Reply ↓  Report •

#3
October 10, 2019 at 03:32:06
Change the 1 in this instruction to a 2 or 3 to reference Column B or C.

Be sure to edit the comment also to avoid future confusion.

lastRw = Cells(Rows.Count, 1).End(xlUp).Row

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 10, 2019 at 04:22:16
Hi DerbyDad03;

I just run the macro. It runs fine but rows are created on top only. I need to create rows where I place the cursor.

For example, I have a sheet having 3 columns. Col A, B & C have data; Col A contains serial No. B filled with name and C with other data. In this way all 50 rows are filled with the data.

I wish to create 6 rows each after Row 1(ser #1), 2(ser #2), 3(ser #3) and so on, so that after each original row, there will be additional 6 rows to be created by pushing rest of the rows down.

Please help. I would love to learn how this to be changed if I wish in future to create rows upward and downward from the point of cell.

Thanks and very best regards


Reply ↓  Report •

#5
October 10, 2019 at 06:10:19
Original requirement:

I need to insert 6 blank rows after each row of data using formula or macro or VB

New requirement:

I need to create rows where I place the cursor.

But also...

I wish to create 6 rows each after Row 1(ser #1), 2(ser #2), 3(ser #3) and so on, so that after each original row, there will be additional 6 rows to be created by pushing rest of the rows down.

"Where I place the cursor" and "after Row 1" are 2 totally different things, so I need to know what you really want.

This version of the code should address the "after Row 1" and "pushing the rest of the rows down" requirements. In other words, it will leave the Row 1 data in Row 1 and move everything else down.

As far as the "cursor placement" requirement, you'll need to explain how that relates to "after Row 1" requirement.

Sub InsertSixRows()

'Disable Screen Upadating so code runs faster
    Application.ScreenUpdating = False

'Determine last Row with data in Column 2 (B)
    lastRw = Cells(Rows.Count, 2).End(xlUp).Row

'Start at bottom of sheet and insert Rows
   For rw = lastRw To 2 Step -1
      Rows(rw & ":" & rw + 5).EntireRow.Insert
   Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
October 12, 2019 at 01:43:12
Appreciated for your time and efforts. I am indebted
best regards

Reply ↓  Report •

Ask Question