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
> >
>
>

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
> >
>
>
>

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
>