Prev: How to change the font size of Data Validation in drop-down me
Next: Print To pdf Not Working
From: Brian on 22 Apr 2010 10:40
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
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: How to change the font size of Data Validation in drop-down me
Next: Print To pdf Not Working