VBScript - Sort BY and Sort EACH column on a Microsoft Excel Worksheet
Sort BY and Sort EACH column on a Microsoft Excel Worksheet
First it SORTS BY each column, treating each row as a single record.
This ASSUMES that excel's sort is stable.
Then it SORTS EACH column individually.
Sorting one column does not affect the order of the other columns.
xlAscending = 1
xlYes = 1
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)
'Add some data to excel
ws.Range("a1:d1").value = array("LAST NAME", "FIRST NAME", "AKA" , "AGE")
ws.Range("a2:d2").value = array("Parker" , "Peter" , "Spiderman" , 18 )
ws.Range("a3:d3").value = array("Parker" , "Benjamin" , "Uncle Ben" , 70 )
ws.Range("a4:d4").value = array("Myer" , "Ken" , "Dr. Scripto?", 3.14 )
ws.Range("a5:d5").value = array("Parker" , "May" , "Aunt May" , 70 )
ws.UsedRange.ColumnWidth = 20
'SORT BY each column, with last column having least priority
Set objRange = ws.UsedRange
For col = objRange.Columns.Count To 1 Step -1
WScript.Sleep 1000
WScript.Echo "SORTING BY column " & col
Set objRange2 = ws.Cells(1, col)
objRange.Sort objRange2, xlAscending, , , , , , xlYes
Next
'SORT EACH column individually
Set objRange = ws.UsedRange
For col = 1 To objRange.Columns.Count
WScript.Sleep 1000
WScript.Echo "SORTING column " & col
Set objRange = ws.Columns(col)
Set objRange2 = ws.Cells(1, col)
objRange.Sort objRange2, xlAscending, , , , , , xlYes
Next
[Via]