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 09:00
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.
From: Dave Peterson on 22 Apr 2010 09:09
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
From: Brian on 22 Apr 2010 09:10
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" 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.
From: Brian on 22 Apr 2010 10:20
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.

"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
> .
>
From: Brian on 22 Apr 2010 10:27
Got it... changed LastRow to be....

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

"Brian" wrote:

> 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.
>
> "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
> > .
> >
 |  Next  |  Last
Pages: 1 2
Prev: How to change the font size of Data Validation in drop-down me
Next: Print To pdf Not Working