Forum Discussion
Solved
Excel Macro Help
I have a spreadsheet that I am looking to build a macro to help with formatting.
Column A is the identify cell, that is an ID #.
If there is a data in Column G, then I need to add a row below, enter in the data from Columns A-C, into the new row, Copy/Paste the data from Column G-I, into Columns D-F.
There could be data in Column G-I, that needs to be into a new row, having the same data from A-C being copied, but also Column J-L, and Column M-O.
Not all the time will be there be data in G-I, there might only be data in Column J-L, or only in Column M-O, or all three.
I am not sure how to build my macro to show that if there is data in those columns, to add a row below and to move the data below the first part. Attached is a sample of the layout of the report.
Any help would be appreciated.
Thank you,
Dawn Fuhrman
If there is sensitive data (names, addresses and phone numbers of real people) in your file then please delete the file from your post.
Sub rearrange() Dim i, j, k As Long k = Range("A" & rows.Count).End(xlUp).Row * 4 For i = 2 To k For j = 13 To 7 Step -3 If Cells(i, j).Value <> "" Then Cells(i + 1, 1).EntireRow.Insert Range(Cells(i + 1, 4), Cells(i + 1, 6)).Value = Range(Cells(i, j), Cells(i, j + 2)).Value Range(Cells(i + 1, 2), Cells(i + 1, 3)).Value = Range(Cells(i, 2), Cells(i, 3)).Value 'next 3 lines are for formatting of column A, this might cause the code run significantly longer 'you can delete these 3 lines to return e.g. Plan ID 1253 instead of 001253 Cells(i, 1).Copy Cells(i + 1, 1).PasteSpecial xlValues Cells(i + 1, 1).PasteSpecial xlFormats Else End If Next j Next i Application.CutCopyMode = False End SubYou can try this code. Lines 19 to 21 of the code are for the formatting in column A. This might cause the code to run significantly longer. If it's ok to return e.g. Plan ID 1253 instead of 001253 you can comment out these lines.
If there is sensitive data (names, addresses and phone numbers of real people) in your file then please delete the file from your post.
Sub rearrange() Dim i, j, k As Long k = Range("A" & rows.Count).End(xlUp).Row * 4 For i = 2 To k For j = 13 To 7 Step -3 If Cells(i, j).Value <> "" Then Cells(i + 1, 1).EntireRow.Insert Range(Cells(i + 1, 4), Cells(i + 1, 6)).Value = Range(Cells(i, j), Cells(i, j + 2)).Value Range(Cells(i + 1, 2), Cells(i + 1, 3)).Value = Range(Cells(i, 2), Cells(i, 3)).Value 'next 3 lines are for formatting of column A, this might cause the code run significantly longer 'you can delete these 3 lines to return e.g. Plan ID 1253 instead of 001253 Cells(i, 1).Copy Cells(i + 1, 1).PasteSpecial xlValues Cells(i + 1, 1).PasteSpecial xlFormats Else End If Next j Next i Application.CutCopyMode = False End SubYou can try this code. Lines 19 to 21 of the code are for the formatting in column A. This might cause the code to run significantly longer. If it's ok to return e.g. Plan ID 1253 instead of 001253 you can comment out these lines.
- You are amazing! This is exactly what I was looking for. Thank you! It is fake data.
