VBA code for inserting multiple rows, copying and transposing data

B

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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
Barry NP said:
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.
Click to expand...
Great news! Thanks for the follow-up. :)
 
Upvote 0
Peter_SSs said:
Great news! Thanks for the follow-up. :)
Click to expand...

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
Barry NP said:
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.
Click to expand...
Sorry, I think I misread your previous post. I will look at this again when I can.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VBA codes to replace all cells containing a certain value
Replies
2
Views
429
Snakehips
Snakehips
I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
666
ItalianPlatinum
I
H
  • Solved
Retrieve the intersection of multiple cells with respect to columns and rows
2
Replies
10
Views
165
mumps
mumps
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
120
Peter_SSs
Peter_SSs
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
675
Domenic
D

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

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