Dear Forum members,

I have a large table of numeric data that often changes in size (pls see an
example excerpt below) and I would like to use command button to automate the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187

matt3542 wrote:
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

How about - given there is enough room (<65000 rows) - you select (with
autofilter) all non-empty fields in the columns B, copy them behind the
last non-empty cell in column A, then do the same for columns C and
following, and in the end sort column A the way you want it to?

HTH,

Lars

Matt,

Can can use the built-in feature which is Macro recodring
(ToolsMacrosRecord...)

Here's the code:

Sub SortClmA()
' Select rows that have data, say row 2 to 7
Rows("2:7").Select
' Sort data Ascending in column A
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
' move focus to cell A2
Range("A2").Select
End Sub

--
HTP
Adnan




"matt3542" wrote:

Dear Forum members,

I have a large table of numeric data that often changes in size (pls see an
example excerpt below) and I would like to use command button to automate the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187

Recorded macro
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 8/8/2008 by Donald B. Guillett
'

'
Range("A1:D1").Select
Range("A1:B5").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
cleaned up

sub sortem()
lr=cells(rows.count,"a").end(xlup).row
Range("A1:d" & lr).Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:= _
xlGuess, Orientation:=xlTopToBottom
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"matt3542" wrote in message
...
Dear Forum members,

I have a large table of numeric data that often changes in size (pls see
an
example excerpt below) and I would like to use command button to automate
the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a
way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187

hi,
or somethng like this:
Sub Macro2()
Selection.CurrentRegion.Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

"matt3542" wrote:

Dear Forum members,

I have a large table of numeric data that often changes in size (pls see an
example excerpt below) and I would like to use command button to automate the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187

Apologies for the late reply, I have been away on annual leave. Thankyou so
much for taking the time to advise, this worked perfectly.

Regards
Matt

"Adnan" wrote:

Matt,

Can can use the built-in feature which is Macro recodring
(ToolsMacrosRecord...)

Here's the code:

Sub SortClmA()
' Select rows that have data, say row 2 to 7
Rows("2:7").Select
' Sort data Ascending in column A
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
' move focus to cell A2
Range("A2").Select
End Sub

--
HTP
Adnan




"matt3542" wrote:

Dear Forum members,

I have a large table of numeric data that often changes in size (pls see an
example excerpt below) and I would like to use command button to automate the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187

Apologies for the late reply, I have been away on annual leave. Thankyou so
much for taking the time to advise, this method also worked perfectly.

Regards
Matt

"Lars Uffmann" wrote:

matt3542 wrote:
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

How about - given there is enough room (<65000 rows) - you select (with
autofilter) all non-empty fields in the columns B, copy them behind the
last non-empty cell in column A, then do the same for columns C and
following, and in the end sort column A the way you want it to?

HTH,

Lars

Many thanks Don that did the trick, much appreciated
Regards
Matt

"Don Guillett" wrote:

Recorded macro
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 8/8/2008 by Donald B. Guillett
'

'
Range("A1:D1").Select
Range("A1:B5").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
cleaned up

sub sortem()
lr=cells(rows.count,"a").end(xlup).row
Range("A1:d" & lr).Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:= _
xlGuess, Orientation:=xlTopToBottom
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"matt3542" wrote in message
...
Dear Forum members,

I have a large table of numeric data that often changes in size (pls see
an
example excerpt below) and I would like to use command button to automate
the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a
way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187

Apologies for the late reply, I have been away on annual leave. Thankyou
David, that worked brilliantly.
Regards
Matt

"David" wrote:

hi,
or somethng like this:
Sub Macro2()
Selection.CurrentRegion.Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

"matt3542" wrote:

Dear Forum members,

I have a large table of numeric data that often changes in size (pls see an
example excerpt below) and I would like to use command button to automate the
way it is sorted. Broadley speaking I would like all the data to appear in
one continuous vertical list in ascending order in column A. Is there a way
VBA can do this? Many thanks, any help gratefully received, Matt

col

A B C
D
0.245879 0.84654654 0.6846846
0.85684684
0.984875 0.65465465 0.47665
0.84687465
0.846543 0.32184987 0.351548
0.145987
0.321456 0.984221 0.651287
0.898516542
0.6846543 0.8422855 0.987354321
0.58714187