Excel sorting in VBScript

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.

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

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.


"Bob Phillips" wrote:

> 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
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Gary Foreman" <Gary [email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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


--
Regards,
Tom Ogilvy


"Gary Foreman" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> "Bob Phillips" wrote:
>
> > 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
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Gary Foreman" <Gary [email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >