Sorting a Range using VBA

[SystemHack]

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 ?

 

[Nigel]

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.

 

[SystemHack]

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]

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 ?

 

[Nigel]

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

 

[Nigel]

sorry meant to use Sheets not Sheet !!

--
Cheers
Nigel

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



in message news:[email protected]...

 

[Mike Fogleman]

Dim NumCount As Long

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

Mike F

 

[Alok]

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

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

 

[Tom Ogilvy]

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.

 

[STEVE BELL]

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