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 -