ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   sorting unknown range (https://www.excelbanter.com/excel-discussion-misc-queries/162864-sorting-unknown-range.html)

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

D.
sorting unknown range
 
Hi,
What determines the range?

Dave Peterson
sorting unknown 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

peyman
sorting unknown range
 
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
sorting unknown range
 
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

peyman
sorting unknown range
 
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
sorting unknown range
 
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

peyman
sorting unknown range
 
hi Dave,
the second code doesn't work.there's an Error,91:"object variable or with
block variable not set"

"Dave Peterson" wrote:

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

peyman
sorting unknown range
 
Oh sorry.I found my mistake.now it is working.I have one more request.Can you
please explain briefly your first code line by line,some lines like goto 0,
=Nothing or Resume next is not clear to me.
thanx for your time Dave

"Dave Peterson" wrote:

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

Dave Peterson
sorting unknown 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

myRng is an object variable (a range object to be specific)

set myrng = nothing
tells is the equivalent of setting a number to 0. I want to make sure that my
variable is initialized to 0.

application.inputbox(prompt:="...", type:=8)
expects the user to select a range (type:=8 is the portion that does that).

If the user hits cancel, then that line will cause an error.

To avoid that possible error, I tell excel to ignore any errors that can happen
with "on error resume next". But as soon as I'm done trying to get that range,
I'll turn error checking back to excel ("on error goto 0"). It doesn't really
mean to goto 0 (like a line number). It's the syntax to tell excel that it
should handle any future errors.

After trying to get the range from the user, I check to see what myRng is. If
the user hit cancel, then that variable didn't change (from Nothing). So I just
check with this line: "If myRng is nothing then".



peyman wrote:

Oh sorry.I found my mistake.now it is working.I have one more request.Can you
please explain briefly your first code line by line,some lines like goto 0,
=Nothing or Resume next is not clear to me.
thanx for your time Dave

"Dave Peterson" wrote:

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


--

Dave Peterson

peyman
sorting unknown range
 
thanks a lot Dave for your exolanation.

"Dave Peterson" wrote:

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

myRng is an object variable (a range object to be specific)

set myrng = nothing
tells is the equivalent of setting a number to 0. I want to make sure that my
variable is initialized to 0.

application.inputbox(prompt:="...", type:=8)
expects the user to select a range (type:=8 is the portion that does that).

If the user hits cancel, then that line will cause an error.

To avoid that possible error, I tell excel to ignore any errors that can happen
with "on error resume next". But as soon as I'm done trying to get that range,
I'll turn error checking back to excel ("on error goto 0"). It doesn't really
mean to goto 0 (like a line number). It's the syntax to tell excel that it
should handle any future errors.

After trying to get the range from the user, I check to see what myRng is. If
the user hit cancel, then that variable didn't change (from Nothing). So I just
check with this line: "If myRng is nothing then".



peyman wrote:

Oh sorry.I found my mistake.now it is working.I have one more request.Can you
please explain briefly your first code line by line,some lines like goto 0,
=Nothing or Resume next is not clear to me.
thanx for your time Dave

"Dave Peterson" wrote:

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


--

Dave Peterson


All times are GMT +1. The time now is 05:30 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com