Transpose Multiple Rows into a Single Column

**vnascimento** · 06-03-2013, 04:22 PM

Hi I have a spreadsheet with numbers, this is an example:

480,000 600,000 720,000
520,000 650,000 780,000
300,000 375,000 450,000
432,000 540,000 648,000
260,000 325,000 390,000
304,000 380,000 456,000
340,000 425,000 510,000
304,000 380,000 456,000

and want them to look like:

480,000
520,000
300,000
432,000
260,000
304,000
340,000
304,000

600,000
650,000
375,000
540,000
325,000
380,000
425,000
380,000

720,000
780,000
450,000
648,000
390,000
456,000
510,000
456,000

But the actual data set is much larger, 23 columns and 351 rows for a total of over 8,000 cells. But the same idea as the smaller example set above. Any way to automate this? I found this function online:

Please Login or Register  to view this content.

Sub undo()
Dim r As Range
Set r = Range("a1").End(xlDown).Offset(1, 0)
Set r = Range(r, Cells(Rows.Count, "A").End(xlUp))
r.EntireRow.Delete

End Sub

Which seems to work but only for the first 4 columns as opposed to all 23, anyone know how to edit or redo it to make it work for the full data set? Thank you.

**stanleydgromjr** · 06-03-2013, 10:31 PM

vnascimento,

With your raw data in worksheet Sheet1, beginning in cell A1, for a variable number of rows and columns.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

**arlu1201** · 06-04-2013, 01:01 AM

vnascimento,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

**jindon** · 06-04-2013, 01:32 AM

Try

Please Login or Register  to view this content.

Edit:
Missed

Which seems to work but only for the first 4 columns as opposed to all 23

**kalak** · 06-04-2013, 02:29 AM

Originally Posted by vnascimento

...
Which seems to work but only for the first 4 columns as opposed to all 23, anyone know how to edit or redo it to make it work for the full data set? Thank you.

Your code probably only worked for 4 columns because the code line

j = Range("A1").End(xlToRight).Column

returned j as 4. i.e. probably your Cells(1,5) was blank even if your Column(5) and further columns weren't.

Using CurrentRegion could be OK if you don't have an entire blank column in your first 22 columns. If you do have such a blank column then probably best to clarify a bit further whether or not you want a blank section in your processed column. Or whether you want the code to stop at the first entirely blank column.

**stanleydgromjr** · 06-04-2013, 08:47 AM

vnascimento,

The following macro will adjust for a varying number of rows and columns, and, it will ignore blank columns, and, it will ignore blank cells in a column.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.