sorting variable sized range - Xtreme Visual Basic Talk

06-27-2009, 11:50 AM

eng_khalid eng_khalid is offline

Newcomer

Join Date: Jun 2009

Posts: 6

sorting variable sized range

is there a way to sort a variable sized range?

Quote:

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=ActiveCell. _
Offset(-1, 0).Range("A1:A41"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange ActiveCell.Offset(-2, -17).Range("A1:AI42")
.Header = xlYes
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

that's the code...is there a way to replace the " Range("A1:A41") " with a code that is flexible with the size of range?
I tried Range.CurrentRegion but it didn't work

any ideas
thnx in advance

06-27-2009, 12:21 PM

dunk123

Newcomer

Join Date: Jun 2009

Posts: 17

Try modifying this to your needs:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("f2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

Posting Rules

You may not post new threads

You may not post replies

You may not post attachments

You may not edit your posts

BB code is On

Smilies are On

[IMG] code is Off

HTML code is Off

Forum Rules