Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel sorting in VBScript
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 wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel sorting in VBScript
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" wrote in message ... 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 wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBScript from Excel | Excel Discussion (Misc queries) | |||
Excel & VBScript | Excel Programming | |||
Using excel through vbscript | Excel Programming | |||
VBScript code behind Excel | Excel Programming | |||
VBScript Automation of Excel Spreadsheet Sorting & Filtering | Excel Programming |