Transpose

[Sanderch]

Hi Guys - I have tried for about 2 hrs to find a solution for this. I'm sure you experts will have a simple solution

I have a spreadsheet - row 1 is headers the row 2 to 11 is data for one client (repeated every 10 rows). Column L contains a different value for the 10 row. this is then repeated for around 20,000 rows.

So I'm looking for a macro or VBA code to take the data from column L row 2 to 11 (the 10 rows for that client) and paste it into column M as transpose. (simple) my issues start as I then want to delete row 9 to 11 and then offset to start on row 3 to row 12 (next 10 rows)

Instead of 10 rows I want 1 row with the 10 values in Column L transposed from M to V and then the 9 rows below deleted and the looped to convert the whole 20,000 rows

Please help.

Chris

 

[steve the fish]

What does this mean?

my issues start as I then want to delete row 9 to 11

Why 9-11?

 

[Sanderch]

Sorry you correct it should be 3 to 11

 

[steve the fish]

Or 2 to 11?

 

[steve the fish]

Im right in thinking row 12, row 23 etc are headers?

 

[Sanderch]

No, to clarify it will be the first row of data for each client that I need to Keep. So row 1 is the sheet headers. row 2 is data source 1 - all data from A to K will be the same info - but column L is a value. this is repeated 10 time down over with different values in column L - What I want is have 1 row rather than 10 and have the data from column L transposed to M to V with new headers based on the reason for the value. so I will keep data source1 (row 2) data source 2 (row 13) data source 3 (row 24) etc. the data has come from a CSV export from data I don't own. I need the data in 1 row rather than 10 rows but over 20,000 rows will take me some time to cut and paste - I wanted a macro or VBA code to help transpose this data and delete the 9 rows below (once transposed)

Hope this makes more sense

 

[Fluff]

Do you have anything in rows 12, 23 etc or are they blank?
Also do you want to keep them?

 

[Fluff]

If you want to keep rows 12,23 etc try this

Sub CopyTranspose()

   Dim Rng As Range
   Dim Cnt As Long
   
   For Cnt = 2 To Range("A" & Rows.Count).End(xlUp).Row Step 11
      Range("L" & Cnt).Resize(, 10).Value = Application.Transpose(Range("L" & Cnt).Resize(10).Value)
      If Rng Is Nothing Then
         Set Rng = Range("A" & Cnt + 1).Resize([COLOR=#ff0000]9[/COLOR])
      Else
         Set Rng = Union(Rng, Range("A" & Cnt + 1).Resize([COLOR=#ff0000]9[/COLOR]))
      End If
   Next Cnt
   Rng.EntireRow.Delete
End Sub

However, if you want to get rid of those rows, change the 2 values in red from 9 to 10