sorting unknown range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125,
whatever) by MACRO?
thanx
 
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
 
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).
 
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
Thanx Dave, it works well but I was wondering if I can only select the range
Click to expand...
 
hi Dave,
the second code doesn't work.there's an Error,91:"object variable or with
block variable not set"
 
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
 
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".


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
Click to expand...
 
thanks a lot Dave for your exolanation.

Dave Peterson said:
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".
Click to expand...
 
Back
Top