Multiple Columns to Single Column

[rrtts]

In my worksheet, I have about 30 column headings...each with varying amount of cells that contain data below the headings.

What I'm trying to figure out how to do is copy the headings that have cells that contain data and the actual cells that have data into a single column.

Obviously the "record macro" isn't going to assist me much with trying to figure out the code to do this...so hopefully one of you brilliant excel/vb programmers can lend a hand.

I've tried to illustrate what I'm trying to achieve in a worksheet (attached).

Appreciate any and all help. Thanks a bunch.

[stanleydgrom]

rrtts,

Save your workbook as Test.xls

In TEST.xls put the word AFTER in cell A20

Copy the following code into a Module in Test.xls


[vba]

Option Explicit

Sub MultipleColumnsToSingleColumn()
'
' MultipleColumnsToSingleColumn Macro
' Macro created 12/06/2006 by Stanley D. Grom, Jr.
'
'
Dim lngLastColumn As Long
Dim c As Long
Dim r As Long
Dim m As Long
m = 21
Application.ScreenUpdating = False
Sheets("Sheet1").Cells(2, 1).Select
lngLastColumn = Sheets("Sheet1").Range("IV2").End(xlToLeft).Column
For c = 1 To lngLastColumn
For r = 2 To 18
If Cells(r, c).Value <> "" Then
Cells(r, c).Copy Destination:=Cells(m, 1)
m = m + 1
End If
Next r
Next c
Application.ScreenUpdating = True
End Sub

[/vba]


Have a great day,
Stan

I am using Windows XP Professional SP2 and Excel 2003 SP2.

[lucas]

That works pretty slick Stan. I just put your code in the same workbook...made a copy of sheet 1 for reference and it gets pretty close to what the Original poster was trying to accomplish.

[rrtts]

Zoinks. That is very slick indeed...I think I can make this work for my project.

Appreciate the help. You rock.