Sorting a Range using VBA

The following is literally what you asked .....

Range(Cells(1, "B"), Cells(2, NumCount)).Sort Key1:=Cells(1, NumCount)

but be careful in specifying the range sort keys. You can also use the
number of the column eg 2 not "B" as you asked.
also note that when defining a range using Cells the first value is the row
number, the second the column.
--
Cheers
Nigel



"SystemHack" <SystemHack.1ud6ad_1125047139.4479@excelforum-nospam.com> wrote
in message news:SystemHack.1ud6ad_1125047139.4479@excelforum-nospam.com...
>
> Assume that I have a variable "NumCount" and I want to sort the range of
> cells from (1,B) to (2,NumCount), sort by the first column accending
> order. What kind of code could I use ?
>
>
> --
> SystemHack
> ------------------------------------------------------------------------
> SystemHack's Profile:
http://www.excelforum.com/member.php...o&userid=26614
> View this thread: http://www.excelforum.com/showthread...hreadid=399388
>

Thanks a ton. The problem was I didn't know you could embed the Cells command into a Range function. That made everyting sooo much easier. Exactly what I as looking for thanks !

Ok I can get this to work on the same page but not on a different sheet. This is the code I am trying to use.


Private Sub CommandButton1_Click()

Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Cells(1, 2)

End Sub

Any ideas why this wouldn't work ?

the sort key must be defined in context as well.

Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Sheet(2).Cells(1, 2)

or

With Sheet(2)
.Range(Cells(1,2),Cells(12,2)).Sort Key1:=.Cells(1,2)
End With
--
Cheers
Nigel



"SystemHack" <SystemHack.1udbug_1125054321.8808@excelforum-nospam.com> wrote
in message news:SystemHack.1udbug_1125054321.8808@excelforum-nospam.com...
>
> Ok I can get this to work on the same page but not on a different sheet.
> This is the code I am trying to use.
>
>
> Private Sub CommandButton1_Click()
>
> Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Cells(1, 2)
>
> End Sub
>
> Any ideas why this wouldn't work ?
>
>
> --
> SystemHack
> ------------------------------------------------------------------------
> SystemHack's Profile:
http://www.excelforum.com/member.php...o&userid=26614
> View this thread: http://www.excelforum.com/showthread...hreadid=399388
>

sorry meant to use Sheets not Sheet !!

--
Cheers
Nigel



"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:eyvwOKjqFHA.3424@TK2MSFTNGP14.phx.gbl...
> the sort key must be defined in context as well.
>
> Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Sheet(2).Cells(1, 2)
>
> or
>
> With Sheet(2)
> .Range(Cells(1,2),Cells(12,2)).Sort Key1:=.Cells(1,2)
> End With
> --
> Cheers
> Nigel
>
>
>
> "SystemHack" <SystemHack.1udbug_1125054321.8808@excelforum-nospam.com>
wrote
> in message news:SystemHack.1udbug_1125054321.8808@excelforum-nospam.com...
> >
> > Ok I can get this to work on the same page but not on a different sheet.
> > This is the code I am trying to use.
> >
> >
> > Private Sub CommandButton1_Click()
> >
> > Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Cells(1, 2)
> >
> > End Sub
> >
> > Any ideas why this wouldn't work ?
> >
> >
> > --
> > SystemHack
> > ------------------------------------------------------------------------
> > SystemHack's Profile:
> http://www.excelforum.com/member.php...o&userid=26614
> > View this thread:
http://www.excelforum.com/showthread...hreadid=399388
> >
>
>

Dim NumCount As Long

NumCount = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:B" & NumCount).Sort Key1:=Range("A1")
End Sub

Mike F
"SystemHack" <SystemHack.1ud6ad_1125047139.4479@excelforum-nospam.com> wrote
in message news:SystemHack.1ud6ad_1125047139.4479@excelforum-nospam.com...
>
> Assume that I have a variable "NumCount" and I want to sort the range of
> cells from (1,B) to (2,NumCount), sort by the first column accending
> order. What kind of code could I use ?
>
>
> --
> SystemHack
> ------------------------------------------------------------------------
> SystemHack's Profile:
> http://www.excelforum.com/member.php...o&userid=26614
> View this thread: http://www.excelforum.com/showthread...hreadid=399388
>

Is it preferable to change the other Cells to .Cells as well?

With Sheet(2)
.Range(.Cells(1,2),.Cells(12,2)).Sort Key1:=.Cells(1,2)
End With

Alok

"Nigel" wrote:

> the sort key must be defined in context as well.
>
> Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Sheet(2).Cells(1, 2)
>
> or
>
> With Sheet(2)
> .Range(Cells(1,2),Cells(12,2)).Sort Key1:=.Cells(1,2)
> End With
> --
> Cheers
> Nigel
>
>
>
> "SystemHack" <SystemHack.1udbug_1125054321.8808@excelforum-nospam.com> wrote
> in message news:SystemHack.1udbug_1125054321.8808@excelforum-nospam.com...
> >
> > Ok I can get this to work on the same page but not on a different sheet.
> > This is the code I am trying to use.
> >
> >
> > Private Sub CommandButton1_Click()
> >
> > Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Cells(1, 2)
> >
> > End Sub
> >
> > Any ideas why this wouldn't work ?
> >
> >
> > --
> > SystemHack
> > ------------------------------------------------------------------------
> > SystemHack's Profile:
> http://www.excelforum.com/member.php...o&userid=26614
> > View this thread: http://www.excelforum.com/showthread...hreadid=399388
> >
>
>
>

It isn't preferable, it is required in this case where Sheet(2) isn't the
active sheet. Otherwise, range refers to Sheet(2) and the unqualified
cells refers to the activesheet (or the sheet containing the code if in a
worksheet module). In any event, this situation is guaranteed to raise an
error.

--
Regards,
Tom Ogilvy
"Alok" <Alok@discussions.microsoft.com> wrote in message
news:AA0AD6E5-553B-4859-B8BD-4B42BE4CF6C4@microsoft.com...
> Is it preferable to change the other Cells to .Cells as well?
>
> With Sheet(2)
> .Range(.Cells(1,2),.Cells(12,2)).Sort Key1:=.Cells(1,2)
> End With
>
> Alok
>
> "Nigel" wrote:
>
> > the sort key must be defined in context as well.
> >
> > Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Sheet(2).Cells(1,
2)
> >
> > or
> >
> > With Sheet(2)
> > .Range(Cells(1,2),Cells(12,2)).Sort Key1:=.Cells(1,2)
> > End With
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "SystemHack" <SystemHack.1udbug_1125054321.8808@excelforum-nospam.com>
wrote
> > in message
news:SystemHack.1udbug_1125054321.8808@excelforum-nospam.com...
> > >
> > > Ok I can get this to work on the same page but not on a different
sheet.
> > > This is the code I am trying to use.
> > >
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Cells(1, 2)
> > >
> > > End Sub
> > >
> > > Any ideas why this wouldn't work ?
> > >
> > >
> > > --
> > > SystemHack
> >
> ------------------------------------------------------------------------
> > > SystemHack's Profile:
> > http://www.excelforum.com/member.php...o&userid=26614
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=399388
> > >
> >
> >
> >

Tom,

Thanks for pointing this out!

It may explain some of the problems I keep running into with specifying
ranges and cells...
(I was just getting ready to write a request to help...)

--
steveB

Remove "AYN" from email to respond
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OmyXNwjqFHA.1336@TK2MSFTNGP11.phx.gbl...
> It isn't preferable, it is required in this case where Sheet(2) isn't the
> active sheet. Otherwise, range refers to Sheet(2) and the unqualified
> cells refers to the activesheet (or the sheet containing the code if in a
> worksheet module). In any event, this situation is guaranteed to raise
> an
> error.
>
> --
> Regards,
> Tom Ogilvy
> "Alok" <Alok@discussions.microsoft.com> wrote in message
> news:AA0AD6E5-553B-4859-B8BD-4B42BE4CF6C4@microsoft.com...
>> Is it preferable to change the other Cells to .Cells as well?
>>
>> With Sheet(2)
>> .Range(.Cells(1,2),.Cells(12,2)).Sort Key1:=.Cells(1,2)
>> End With
>>
>> Alok
>>
>> "Nigel" wrote:
>>
>> > the sort key must be defined in context as well.
>> >
>> > Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Sheet(2).Cells(1,
> 2)
>> >
>> > or
>> >
>> > With Sheet(2)
>> > .Range(Cells(1,2),Cells(12,2)).Sort Key1:=.Cells(1,2)
>> > End With
>> > --
>> > Cheers
>> > Nigel
>> >
>> >
>> >
>> > "SystemHack" <SystemHack.1udbug_1125054321.8808@excelforum-nospam.com>
> wrote
>> > in message
> news:SystemHack.1udbug_1125054321.8808@excelforum-nospam.com...
>> > >
>> > > Ok I can get this to work on the same page but not on a different
> sheet.
>> > > This is the code I am trying to use.
>> > >
>> > >
>> > > Private Sub CommandButton1_Click()
>> > >
>> > > Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Cells(1, 2)
>> > >
>> > > End Sub
>> > >
>> > > Any ideas why this wouldn't work ?
>> > >
>> > >
>> > > --
>> > > SystemHack
>> >
>> ------------------------------------------------------------------------
>> > > SystemHack's Profile:
>> > http://www.excelforum.com/member.php...o&userid=26614
>> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=399388
>> > >
>> >
>> >
>> >
>
>