Variable Range Sort

[Brian]

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]

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]

Oh I forgot to add onto this.... I am working it into this macro which I
could use a tab of help with to not mess up the sorting too.... I need to
change ActiveSheet.Rows("5:5").Select to be variable also to select the
last filled out row... how could i select the row of the selected cell? I
could select the last cell at the end of the column with...

ActiveSheet.Range("A3").End(xlDown).Offset(1, 0).Select

The reason I need to do it this way is because below all of this in column A
there is additional data I don't want to sort or insert with.

Sub InsertSheets()
Dim cnt As Integer
Dim pwrd As String

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

ActiveSheet.Unprotect pwrd

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

ActiveSheet.Protect pwrd

End Sub

 

[Brian]

Close, but it keeps sorting the rows below also because I have some data
under the end of the data that is entered in. It needs to only sort A3:L and
the end of the data at that point. If I for example highlight A3:L3 and do
ctrl shift down it get's to the end of my data that I want to sort and stops
prior to data below that does not need to be sorted.

 

[Brian]

Got it... changed LastRow to be....

LastRow = .Range("A3").End(xlDown).Row

 

[Brian]

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