Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting unknown range
hi,
how can I sort an unknown range(the range can be variable, A1:F5, G6:K125, whatever) by MACRO? thanx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting unknown range
Hi,
What determines the range? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP! Unknown range | Excel Worksheet Functions | |||
sorting through unknown source data as it is entered | Excel Worksheet Functions | |||
Sumproduct (Range unknown, needs Search) | Excel Worksheet Functions | |||
Average of Unknown Range | Excel Discussion (Misc queries) | |||
sorting a range | Excel Discussion (Misc queries) |