how to define a range in VBA

[Negda]

I'm trying to create a VBA small program for sorting a range by 3
paramters by using the Recording Macro tool. the table to sort is
varying by the rows each between sorts.

Before the begining of the recording I choose the active cell to be on
the table header row and then entered the following sequensce:
<end> <left arrow> <shift><end><down arrow> (while continue holding
the <shift>) <left aroow> 13 times - to reach the right botom of the
table (some of the culomns are not filled) and then I did the sorting.

following is what was actually recorded:

Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:N1511").Select
Selection.Sort Key1:=ActiveCell.Offset(0, 11).Range("A1"),
Order1:= _
xlAscending, Key2:=ActiveCell.Offset(0, 13).Range("A1"),
Order2:=xlAscending _
, Key3:=ActiveCell.Offset(0, 8).Range("A1"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
End Sub

in the 3rd row the range was transormed to fix range (?)

how to fix it?


Negda

 

[Bob Phillips]

Set rng = Range(Selection,cells(lastrow,lastcol))

rng.Sort Key1:=ActiveCell.Offset(0, 11).Range("A1"), _
Order1:= xlAscending, _
Key2:=ActiveCell.Offset(0, 13).Range("A1"), _
Order2:=xlAscending, _
Key3:=ActiveCell.Offset(0, 8).Range("A1"), _
Order3:=xlAscending, _
Header:= xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:= xlSortNormal
End Sub






Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol() As Long
On Error Resume Next
LastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)