Sorting a Range using VBA
 

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

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" wrote
in message ...

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

Sorting a Range using VBA
 

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 !


--
SystemHack
------------------------------------------------------------------------
SystemHack's Profile: http://www.excelforum.com/member.php...o&userid=26614
View this thread: http://www.excelforum.com/showthread...hreadid=399388

Sorting a Range using VBA
 

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

Sorting a Range using VBA
 

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" wrote
in message ...

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

Sorting a Range using VBA
 

sorry meant to use Sheets not Sheet !!

--
Cheers
Nigel



"Nigel" wrote in message
...
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"
wrote
in message ...

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

Sorting a Range using VBA
 

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" wrote
in message ...

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

Sorting a Range using VBA
 

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" wrote
in message ...

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

Sorting a Range using VBA
 

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" wrote in message
...
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"
wrote
in message
...

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

Sorting a Range using VBA
 

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" wrote in message
...
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" wrote in message
...
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"
wrote
in message
...

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