Thanks so much! I worked some of that into my other question in this
also..... Final result which correctly inserts rows and sorts....

Sub InsertSheets()

Dim cnt As Integer
Dim pwrd As String
Dim wks As Worksheet
Dim LastRow As Long
Dim myRng As Range
pwrd = "XXXX"
cnt = 0
Set wks = ActiveSheet



With wks
LastRow = .Range("A3").End(xlDown).Row
Set myRng = .Range("A3:L" & LastRow)
End With

CntWanted = 0
CntWanted = CntWanted + InputBox("How many rows would you like to add?")

ActiveSheet.Unprotect pwrd

Do
wks.Rows(LastRow + 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove
cnt = cnt + 1
Loop Until cnt = CntWanted

With myRng
.Cells.Sort _
Key1:=.Columns(5), Order1:=xlAscending, _
key2:=.Columns(3), order2:=xlDescending, _
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

ActiveSheet.Protect pwrd

End Sub

"Dave Peterson" wrote:

Dim wks as worksheet
dim LastRow as long
dim myRng as range

set wks = worksheets("SomeSheetNameHere") 'or activesheet '???

with wks
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("A3:L" & lastrow)
end with

with myrng
.cells.sort _
Key1:=.Columns(5), Order1:=xlAscending, _
key2:=.columns(3), order2:=xldescending, _
Header:=xlno, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
end with

I used column A to determine the last used row. You may need to change this.

And I specified that the range did not have headers. Change that if row 3 is a
header row. But don't let excel guess. You know your data better than excel --
and excel can guess wrong!


Brian wrote:

Hello I am trying to setup a macro to sort a variable range. I need to sort
from A3 to L, but the L row would be variable... I know if I select A3:L3 and
then do End(xlDown) that would be a start but I am not sure how exactly to
work this into the sort portion.....

A start is....

Range("A3:L3").Select
Range(Selection, Selection.End(xlDown)).Select

but I am stuck now... I can't figure out how to set the range in sort to
something like that. I need to sort ascending with column E then secondary
with column C.

--

Dave Peterson
.