Excel sorting in VBScript

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary Foreman - 28 Feb 2006 18:18 GMT

I'm having trouble with Excel sorting in a VBScript. I have a number of
cells containing data, starting at row 4. I'm trying to sort each column
from row 4 through to the end using the following code:

Set objRange = objExcel.Range(Chr(64+column)+"4", Chr(64+column)&row ).Select
Set objRange2 = objExcel.Range(Chr(64+column)+"4")
objRange.Sort objRange2,,,,,,,1

objRange is my range, objRange2 is the first cell of the column I wish to
sort.

I also tried:

objRange.Sort objRange,,,,,,,1

The sort method does not work in VBScript, nor do a large number of
variations of it! I know from a lot of reading that some Excel functionality
isn't exposed too well in VBScript...

However, the following works for the entire column, using a header row:

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Sort objRange,,,,,,,1

Any suggestions would be much appreciated.

Reply to this Message

Bob Phillips - 28 Feb 2006 18:34 GMT

Not tested, but try

Set objRange = objExcel.Range(Chr(64+objExcel.column())&"4",
Chr(64+objExcel.column())&row ).Select
Set objRange2 = objExcel.Range(Chr(64+objExcel.column())&"4")
objRange.Sort objRange2,,,,,,,1

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I'm having trouble with Excel sorting in a VBScript. I have a number of
> cells containing data, starting at row 4. I'm trying to sort each column
[quoted text clipped - 21 lines]
>
> Any suggestions would be much appreciated.

Reply to this Message

Gary Foreman - 28 Feb 2006 18:47 GMT

bob, thanks for the quick reply but it doesn't work ;)

it's not selecting my range - the objExcel.column() breaks it - my
'Chr(64+column)' generates the column by letter (i know that I have less than
26 columns) and adding the "4" makes reach column that I wish to sort start
at row 4.

g.

> Not tested, but try
>
[quoted text clipped - 30 lines]
> >
> > Any suggestions would be much appreciated.

Reply to this Message

Tom Ogilvy - 28 Feb 2006 18:56 GMT

Assume objExcel.Column is a reference to the column you want:

Set objRange = objExcel.Range(Chr(64+objExcel.column())&"4",
Chr(64+objExcel.column())&row )
objRange.Sort objRange(1),,,,,,,1

another

With Cells(4,ObjExcel.Column)
.Resize(row-1,1).Sort .Item(1),,,,,,,1
End With

Signature

Regards,
Tom Ogilvy

> bob, thanks for the quick reply but it doesn't work ;)
>
[quoted text clipped - 39 lines]
> > >
> > > Any suggestions would be much appreciated.

Reply to this Message