Excel sorting in VBScript
in [Programming]
|
From: Gary Foreman <Gary on 28 Feb 2006 13:18 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.
From: Bob Phillips on 28 Feb 2006 13:34 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 Foreman(a)discussions.microsoft.com> wrote in message news:665DE7D6-0F0A-4FFD-84BC-311463AD07D3(a)microsoft.com... > 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.
From: Gary Foreman on 28 Feb 2006 13:47 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 Foreman(a)discussions.microsoft.com> wrote in message > news:665DE7D6-0F0A-4FFD-84BC-311463AD07D3(a)microsoft.com... > > 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. > > >
From: Tom Ogilvy on 28 Feb 2006 13:56 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" <GaryForeman(a)discussions.microsoft.com> wrote in message news:B96362A2-4CA5-4A3D-8B98-6192CC3A2C63(a)microsoft.com... > 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 Foreman(a)discussions.microsoft.com> wrote in message > > news:665DE7D6-0F0A-4FFD-84BC-311463AD07D3(a)microsoft.com... > > > 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. > > > > > >
|
Pages: 1 Prev: Pivot Table version (xlPivotTableVersion12) Next: How check if workbook open? |