I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed

There is nothing specifically wrong with what you have. Can you show us where
you declare rngWork and where you define the value of myCol...
--
HTH...

Jim Thomlinson


"Ed from AZ" wrote:

I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed

The code snippit is

Sub Format_Wksh()

Dim rngWork As Excel.Range
Dim numRow As Long
Dim numCol As Long
Dim iRow As Long

wks.Activate

With wks
numRow = .Range("A65536").End(xlUp).Row
numCol = .Range("A1").End(xlToRight).Column
Set rngWork = .Range(Cells(2, 1), Cells(numRow, numCol))

rngWork.Sort Key1:=.Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I specified Excel.Range because I'm also using some late-bound Word
code and I didn't want anything to get confused. mycol is set earlier
in a different sub, but the variable is declared module-wide and is
valid when checked.

I appreciate any insights you have here. I must run now - I'll be
back in about 18 hours.

Ed



On Aug 29, 4:12 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
There is nothing specifically wrong with what you have. Can you show us where
you declare rngWork and where you define the value of myCol...
--
HTH...

Jim Thomlinson



"Ed from AZ" wrote:
I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed- Hide quoted text -

- Show quoted text -

Since your data starts in A1, you could use:

Sub Format_Wksh()

Dim rngWork As Excel.Range
Dim numRow As Long
Dim numCol As Long
Dim iRow As Long

'wks.Activate

With wks
numRow = .Range("A65536").End(xlUp).Row
numCol = .Range("A1").End(xlToRight).Column
'if you qualify the .cells(), you don't have to use wks.activate
Set rngWork = .Range(.Cells(2, 1), .Cells(numRow, numCol))

with rngWork
.cells.sort key1:=.columns(mycol), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

======

This works because rngwork.columns(mycol) is the same as wks.columns(mycol).
Your data started in A1.

You could have used:

with wks
rngwork.sort key1:=.columns(mycol), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

And .columns(mycol) refers to the worksheet--not the range.

This is important if your data doesn't start in column A.

ps. It's your data. So you know if it has a header or not, right? If you do,
then it's better to specify that parm than to let excel guess.



Ed from AZ wrote:

The code snippit is

Sub Format_Wksh()

Dim rngWork As Excel.Range
Dim numRow As Long
Dim numCol As Long
Dim iRow As Long

wks.Activate

With wks
numRow = .Range("A65536").End(xlUp).Row
numCol = .Range("A1").End(xlToRight).Column
Set rngWork = .Range(Cells(2, 1), Cells(numRow, numCol))

rngWork.Sort Key1:=.Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I specified Excel.Range because I'm also using some late-bound Word
code and I didn't want anything to get confused. mycol is set earlier
in a different sub, but the variable is declared module-wide and is
valid when checked.

I appreciate any insights you have here. I must run now - I'll be
back in about 18 hours.

Ed

On Aug 29, 4:12 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
There is nothing specifically wrong with what you have. Can you show us where
you declare rngWork and where you define the value of myCol...
--
HTH...

Jim Thomlinson



"Ed from AZ" wrote:
I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Dave, you nailed it!!

When I tried to set Key1:=rngWork(Cells(1, mycol)), I always got a
Type Mismatch error.

When I used your first code
.cells.sort key1:=.columns(mycol)
it breezed right through!!

Thanks so much!

Ed

PS - I did change that to Header:=xlNo.


On Aug 29, 5:53 pm, Dave Peterson wrote:
Since your data starts in A1, you could use:

Sub Format_Wksh()

Dim rngWork As Excel.Range
Dim numRow As Long
Dim numCol As Long
Dim iRow As Long

'wks.Activate

With wks
numRow = .Range("A65536").End(xlUp).Row
numCol = .Range("A1").End(xlToRight).Column
'if you qualify the .cells(), you don't have to use wks.activate
Set rngWork = .Range(.Cells(2, 1), .Cells(numRow, numCol))

with rngWork
.cells.sort key1:=.columns(mycol), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

======

This works because rngwork.columns(mycol) is the same as wks.columns(mycol).
Your data started in A1.

You could have used:

with wks
rngwork.sort key1:=.columns(mycol), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

And .columns(mycol) refers to the worksheet--not the range.

This is important if your data doesn't start in column A.

ps. It's your data. So you know if it has a header or not, right? If you do,
then it's better to specify that parm than to let excel guess.





Ed from AZ wrote:

The code snippit is

Sub Format_Wksh()

Dim rngWork As Excel.Range
Dim numRow As Long
Dim numCol As Long
Dim iRow As Long

wks.Activate

With wks
numRow = .Range("A65536").End(xlUp).Row
numCol = .Range("A1").End(xlToRight).Column
Set rngWork = .Range(Cells(2, 1), Cells(numRow, numCol))

rngWork.Sort Key1:=.Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I specified Excel.Range because I'm also using some late-bound Word
code and I didn't want anything to get confused. mycol is set earlier
in a different sub, but the variable is declared module-wide and is
valid when checked.

I appreciate any insights you have here. I must run now - I'll be
back in about 18 hours.

Ed

On Aug 29, 4:12 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
There is nothing specifically wrong with what you have. Can you show us where
you declare rngWork and where you define the value of myCol...
--
HTH...

Jim Thomlinson

"Ed from AZ" wrote:
I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Note that rngWork is an explicit range reference, while you later use
non-explicit references (Key1:=Range(Cells). That will only work if the
range being sorted is active.



"Ed from AZ" wrote in message
ups.com...
I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed

So if instead of

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I used

rngWork.Select
Selection.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

It might have a better chance of working? I'll have to give that a
shot.

Ed




On Aug 29, 4:43 pm, <- wrote:
Note that rngWork is an explicit range reference, while you later use
non-explicit references (Key1:=Range(Cells). That will only work if the
range being sorted is active.

"Ed from AZ" wrote in oglegroups.com...



I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed- Hide quoted text -

- Show quoted text -

I think this would work OK:

rngWork.Sort Key1:=rngWork.Cells(2,myCol)....

Adjust the part after Key1 to suit your needs.



"Ed from AZ" wrote in message
ps.com...
So if instead of

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I used

rngWork.Select
Selection.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

It might have a better chance of working? I'll have to give that a
shot.

Ed




On Aug 29, 4:43 pm, <- wrote:
Note that rngWork is an explicit range reference, while you later use
non-explicit references (Key1:=Range(Cells). That will only work if the
range being sorted is active.

"Ed from AZ" wrote in
oglegroups.com...



I'm trying to sort a portion of my worksheet. When my first few
attempts failed, I recorded a macro, then substituted my range for
Selection. Apparently, though, my problem is setting the range for
Key1. Here's what I've got:

rngWork.Sort Key1:=Range(Cells(2, mycol)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I tried
rngSort = .Range(Cells(2, mycol))
rngWork.Sort Key1:=rngSort, Order1:= etc.
but it still won't go.

How can I make this work using the column variable?

Ed- Hide quoted text -

- Show quoted text -