Help with code to sort worksheet, please?

[Ed from AZ]

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

 

[Guest]

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...

 

[Guest]

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]

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

 

[Ed from AZ]

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

 

[Guest]

I think this would work OK:

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

Adjust the part after Key1 to suit your needs.

 

[Dave Peterson]

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]

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.