Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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

Sorting an indeterminable range of data
 

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