hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx

Hi,
What determines the range?

And what keys to use for the sort, and what order and if you have headers and...

peyman wrote:

hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx

--

Dave Peterson

user specifies the range ,I have headers, order is ascending and based on the
last column of the range selection.I want a commandbox do the sort.thank you

"Dave Peterson" wrote:

And what keys to use for the sort, and what order and if you have headers and...

peyman wrote:

hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx

--

Dave Peterson

How about:

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(prompt:="Select your range to sort", _
Default:=Selection.Areas(1).Address, Type:=8).Areas(1)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

With myRng
.Sort key1:=.Columns(.Columns.Count), order1:=xlAscending, _
Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

You may want to add some validation (like multiple columns only, multiple rows
must be selected).

peyman wrote:

user specifies the range ,I have headers, order is ascending and based on the
last column of the range selection.I want a commandbox do the sort.thank you

"Dave Peterson" wrote:

And what keys to use for the sort, and what order and if you have headers and...

peyman wrote:

hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx

--

Dave Peterson


--

Dave Peterson

Thanx Dave, it works well but I was wondering if I can only select the range
on worksheet without using the the inputbox?I mean selecting any range then
pushing the commandbutton?


"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(prompt:="Select your range to sort", _
Default:=Selection.Areas(1).Address, Type:=8).Areas(1)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

With myRng
.Sort key1:=.Columns(.Columns.Count), order1:=xlAscending, _
Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

You may want to add some validation (like multiple columns only, multiple rows
must be selected).

peyman wrote:

user specifies the range ,I have headers, order is ascending and based on the
last column of the range selection.I want a commandbox do the sort.thank you

"Dave Peterson" wrote:

And what keys to use for the sort, and what order and if you have headers and...

peyman wrote:

hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx

--

Dave Peterson


--

Dave Peterson

You can just use the current selection instead.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

set myRng = Selection.areas(1)

With myRng
.Sort key1:=.Columns(.Columns.Count), order1:=xlAscending, _
Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

peyman wrote:

Thanx Dave, it works well but I was wondering if I can only select the range
on worksheet without using the the inputbox?I mean selecting any range then
pushing the commandbutton?

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(prompt:="Select your range to sort", _
Default:=Selection.Areas(1).Address, Type:=8).Areas(1)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

With myRng
.Sort key1:=.Columns(.Columns.Count), order1:=xlAscending, _
Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

You may want to add some validation (like multiple columns only, multiple rows
must be selected).

peyman wrote:

user specifies the range ,I have headers, order is ascending and based on the
last column of the range selection.I want a commandbox do the sort.thank you

"Dave Peterson" wrote:

And what keys to use for the sort, and what order and if you have headers and...

peyman wrote:

hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson