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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
wrote
> in message news:[email protected]...
> >
> > 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
> in message news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]>
> wrote
>> > in message
> news:[email protected]...
>> > >
>> > > 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
>> > >
>> >
>> >
>> >
>
>