VBA macro help variable vertical list blocks transposed to rows

pdf

New Member
Joined
Aug 10, 2009
Messages
3
Hi I need help with code for a macro that transposes blocks of vertical cells to rows, that are each separated by a blank row.

Please note that the vertical blocks vary in size.

ie:

A1 text1
A2 test2
A3 text3
A4
A5 text4
A6 text5
A7 text6
A8 text7
A9
...etc

Transposed, must look like:
A1 text1 B1 text2 C1 text3
A2 text4 B2 text5 C2 text6 D2 text7
...etc

Anyone please help!:confused:
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,796
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Re: VBA macro help varaible vertical list blocks transposed to rows

ok assuming the text is in col A, and we will write the new data from D1 downwards, try the following

Sub colToRow()
Dim LastRow As Integer
Dim j As Integer
Dim SaveJ As Integer

Dim Myvalue As String

Dim CurrValue

LastRow = Range("A65536").End(xlUp).Row
k = 0
l = 0
For j = 1 To LastRow
If Range("A" & j).Value <> "" Then
Range("D1").Offset(k, l).Value = Range("A" & j).Value
l = l + 1
Else
l = 0
k = k + 1
End If


Next j

End Sub
 

pdf

New Member
Joined
Aug 10, 2009
Messages
3
Wow! Thanks so much for the quick reply!

Worked like a charm!!!

CHEERS!
 

Transpose

New Member
Joined
May 18, 2011
Messages
1
Hi there,
I have a massive amount of data that I need to transpose.
It is currently in the following format:
Data requiring transposing is from column C to AZ (ie, 48 columns) and I have one row per day with a years worth of rows (ie, 365 rows).
I want to get all the data into one single vertical column, so that each day takes up 48 rows.
Is there a macro that can do this?
Please advise.
Thanks in advance.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,257
Messages
5,769,075
Members
425,515
Latest member
baltusf

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
Top