Solved Macro to insert rows if cells are occupied and copy data

May 26, 2016 at 14:54:47
Specs: Windows 10
Hi, I need a macro that will insert rows based on if cells in rows contain data. So, if rows I-P contain data I need rows insert below based on how many are occupied. For instance, I8-M8 contain data I need 5 rows inserted below them. If I8-P8 contain data I need 9 rows. Then I also need rows A-G and Q-T copied down. I would greatly appreciate any help. Thanks

See More: Macro to insert rows if cells are occupied and copy data

Report •


#1
May 26, 2016 at 18:22:36
re: " So, if rows I-P contain data I need rows insert below based on how many are occupied"

I:P are columns, not rows.

re: "Then I also need rows A-G and Q-T copied down"

A:G and Q:T are columns, not rows.

re: "For instance, I8-M8 contain data I need 5 rows inserted below them."

You used Row 8 in both of your examples. Is Row 8 the only row that needs to be dealt with or do other rows need to be dealt with?

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


Report •

#2
May 30, 2016 at 14:54:19
Sorry, for my mistake. Yes, I meant columns. No, I need it to deal with all rows in the sheet. Right now that is in the thousands. Thanks for your response.

Report •

#3
May 30, 2016 at 19:01:30
✔ Best Answer
Try the following code.

Assumptions:

1 - Column A contains data in every row. This is the column that is used to determine how many rows need to be dealt with.
2 - Row 1 contains Column Headings and therefore should not be included.
2 - There is no data beyond Column T. To make the code easier to write, I simply copy the entire row as many times as needed, and then clear the values in I:P. This essentially copies down A-G and Q-T by leaving the data in the newly copied row. If there is data beyond Column T that you don't want, that is easy to deal with. Just let me know.

I suggest you try this on a back-up copy of your workbook in case things go terribly wrong. I also suggest that you try it on a shortened version. In order to make the code run quicker, it turns off ScreenUpdating so you won't see anything happening until it's done. It might take a while to do "thousands" of rows so I suggest you try it with just a few rows to see if it does what you expect.

Option Explicit
Sub InsertFill()
Dim lastRw As Long
Dim rw As Long
Dim numRws As Long
Dim insRw As Long
Dim myRng As String
Application.ScreenUpdating = False
'Determine last row with data in Column A
 lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through Rows
   For rw = lastRw To 2 Step -1
'Count Cells in I:Q that contain data
      myRng = Range(Cells(rw, "I"), Cells(rw, "Q")).Address
      numRws = Application.WorksheetFunction.CountA(Range(myRng))
'Insert Rows based on count
        For insRw = rw To rw + numRws - 1
         Cells(insRw + 1, 1).EntireRow.Insert
'Copy entire Row, Clear I:P in new Row
           Cells(insRw, 1).EntireRow.Copy Cells(insRw + 1, 1)
           Range(Cells(insRw + 1, "I"), Cells(insRw + 1, "P")).ClearContents
        Next
   Next
Application.ScreenUpdating = True
End Sub

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


Report •

Related Solutions

#4
June 1, 2016 at 19:30:04
That works great. One thing I forgot to add was instead of having the data in column H copied down I need the data in I-P cut and pasted into column H. For instance, if I3, J3, and K3 have data then it is cut and pasted in H4, H5, and H6, while all A3-G3 and Q3-T3 is copied straigh down into A4,5,6-G4,5,6 and Q4,5,6-T4,5,6.

I'm sorry if I poor at explaining this and I greatly appreciate the help.


Report •

#5
June 1, 2016 at 20:06:38
Before I modify the code, I need to ask: Is there anything else that you "forgot to add"?

Sometimes it gets to the point where more and more add-ons just makes the original code bloated and inefficient, resulting in the need for a complete re-write. I don't want to have to do that.

Please take a very close look at your needs and processes and make sure that there isn't anything else that you need.

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


Report •

#6
June 5, 2016 at 17:16:19
No, I tested it on a few rows and it worked exactly how I need except what I included in the last message.

Report •

#7
June 5, 2016 at 18:48:41
One more question:

Your latest example says "For instance, if I3, J3, and K3 have data then it is cut and pasted in H4, H5, and H6...

Is your I:P data always in contiguous order starting in Column I or could you have data in J, M, & Q? In other words, it's still 3 pieces of data, therefore 3 rows will be inserted, but the original data is not in contiguous order nor does it start in Column I.

Can that happen?

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


Report •


Ask Question