Can you convert a worksheet with multiple columns and multiple rows of data
into 1 column?

CURRENT WORKSHEET STRUCTURE(assume A1=_-Insert, A2=MHS,etc.)

_-Insert _-Insert
MHS MHS-PROP
225,955,0 0,940,0
1.00 1.00
1.00 1.00
0.00 0.00

DESIRED RESULT

_-Insert
MHS
225,955,0
1.00
1.00
0.00
_-Insert
MHS-PROP
0,940,0
1.00
1.00
0.00

Any help is appreciated. Thanks!

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim WS As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim myCell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set WS = ActiveSheet
iLastcol = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = WS.Cells(WS.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = WS.Range(WS.Cells(1, ColNdx), _
WS.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each myCell In myRng
If myCell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next myCell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

WS.Activate
End Sub


Gord Dibben MS Excel MVP

On Sat, 10 Nov 2007 12:06:01 -0800, Darian
wrote:

Can you convert a worksheet with multiple columns and multiple rows of data
into 1 column?

CURRENT WORKSHEET STRUCTURE(assume A1=_-Insert, A2=MHS,etc.)

_-Insert _-Insert
MHS MHS-PROP
225,955,0 0,940,0
1.00 1.00
1.00 1.00
0.00 0.00

DESIRED RESULT

_-Insert
MHS
225,955,0
1.00
1.00
0.00
_-Insert
MHS-PROP
0,940,0
1.00
1.00
0.00

Any help is appreciated. Thanks!

=OFFSET($A$1,MOD(ROWS($A$1:A1)-1,COUNTA($A$1:$A$6)),INT((ROWS($A$1:A1)-1)/COUNTA($A$1:$A$6)))

Copy down as far as needed.


"Darian" wrote:

Can you convert a worksheet with multiple columns and multiple rows of data
into 1 column?

CURRENT WORKSHEET STRUCTURE(assume A1=_-Insert, A2=MHS,etc.)

_-Insert _-Insert
MHS MHS-PROP
225,955,0 0,940,0
1.00 1.00
1.00 1.00
0.00 0.00

DESIRED RESULT

_-Insert
MHS
225,955,0
1.00
1.00
0.00
_-Insert
MHS-PROP
0,940,0
1.00
1.00
0.00

Any help is appreciated. Thanks!

Thanks,

This is EXACTLY what I have been looking for!

Darian

"Teethless mama" wrote:

=OFFSET($A$1,MOD(ROWS($A$1:A1)-1,COUNTA($A$1:$A$6)),INT((ROWS($A$1:A1)-1)/COUNTA($A$1:$A$6)))

Copy down as far as needed.


"Darian" wrote:

Can you convert a worksheet with multiple columns and multiple rows of data
into 1 column?

CURRENT WORKSHEET STRUCTURE(assume A1=_-Insert, A2=MHS,etc.)

_-Insert _-Insert
MHS MHS-PROP
225,955,0 0,940,0
1.00 1.00
1.00 1.00
0.00 0.00

DESIRED RESULT

_-Insert
MHS
225,955,0
1.00
1.00
0.00
_-Insert
MHS-PROP
0,940,0
1.00
1.00
0.00

Any help is appreciated. Thanks!