VBA code for inserting multiple rows, copying and transposing data

Barry NP

New Member
Joined
Jul 18, 2017
Messages
24
Hi all,


I am hoping that the following is possible to be done with vba code. I have searched but am struggling to find code to cover all of this. I have broken this down into 3 separate stages below.


I have a set of data in an excel spreadsheet that runs from column A to Column K and 6000 rows. The columns will always be the same, but the rows can increase over time. A snapshot example of data below.


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Nesling[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Dereck[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Ashely[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Reece[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
</tbody>[/TABLE]


What I am looking at achieving is this (probably with a separate macro for each part):


1) Firstly I need 7 rows inserted between each line so that for e.g. Row 1 will have David Jones, followed by 7 blank rows and on row 9 will be Steve Davies and so on to the last row of data (circa 6000 rows).



2) I then need the newly inserted 7 blank rows to be auto filled with the data of the row above for columns A to C only, so for e.g rows 2 to 8 will show David Jones 10.00 for columns A to C. Then row 9 will show Steve Davies 10.00 and I then need rows 9 to 16 to also show Steve Davies 10.00 and so on. I need this to be done for all data rows to the last row of data. Columns D to K do not need to be auto-filled with the years in the newly inserted blank rows.



3) The last part that I am hoping to achieve is to cut and transpose the years for each row in columns D to K and to paste down in column D.


This needs to be done to the last row of data.


So what I would expect to see is the e.g. below with no data in columns E to K and years in column D.



[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



This is a work project, so any help on this would be very, very gratefully received. I am an intermediate user of VBA and this has so far stumped me.


Keeping my fingers crossed on this one, so if you can assist it would be a massive help to me.


If you need any further clarification, please let me know.


Many thanks,


Barry.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Try this in a copy of your workbook. It assumes a heading row with the posted data starting in row 2 and places the rearranged data in columns N:Q. This placement can be altered if required once we are sure it is doing what you want.

Code:
Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("K" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To 8 * UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    For j = 4 To 11
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3): b(k, 4) = a(i, j)
    Next j
  Next i
  Range("N2").Resize(UBound(b), 4).Value = b
End Sub
 
Last edited:
Upvote 0
Solution
Many thanks for this Peter. I will use this first thing Monday morning and will test it to see if it works as expected. I will let you know the outcome on this. Once again many thanks.
 
Upvote 0
Hi Peter. Just to let you know the code worked like a charm. Cannot thank you enough for this as this will save work colleagues hours of otherwise manual work!
Much appreciated.
Barry.
 
Upvote 0
Hi Peter. Just to let you know the code worked like a charm. Cannot thank you enough for this as this will save work colleagues hours of otherwise manual work!
Much appreciated.
Barry.
Great news! Thanks for the follow-up. :)
 
Upvote 0
Great news! Thanks for the follow-up. :)

Hi Peter.
Following on from the code the code that you provided above which works great, is there a way of using additional code that will format the rows that have been transposed (i.e. the year numbers) so that if a year has red font, this will show as red font in the final output.

If this is possible that would be great.

Many thanks in advance.

Barry.
 
Upvote 0
Do you mean this?
Add this line immediately before the End Sub line
Code:
Columns("Q").Font.Color = vbRed
 
Upvote 0
In an ideal world that would be fine. However not all years will have red font. There will be a mixture of std font and red font in the source data and I need the red font to show in the transposed data. Ideally formatting the transposed data as per the source data. If this is not possible I will think of an alternative way. Many thanks, Barry.
 
Upvote 0
In an ideal world that would be fine. However not all years will have red font. There will be a mixture of std font and red font in the source data and I need the red font to show in the transposed data. Ideally formatting the transposed data as per the source data. If this is not possible I will think of an alternative way. Many thanks, Barry.
Sorry, I think I misread your previous post. I will look at this again when I can.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top