#1
|
|||
|
|||
Macro to sort columns
I have an array of about 340 columns wide and 16 rows long. I need to sort (ascending) each column separately (select column, sort a-z, continue with current selection). It will be quite tedious to do the sorting manually for 340 columns, so I was looking to build a macro to do it for me.
I've tried the following, but can't get it debugged. Of course then I also need to add in a loop to go to the next column, repeat, and eventually end the loop when out of columns with content to sort. Any help in creating the macro will be appreciated, else I'm back to just doing each column manually. Thanks in advance, Andrew Sub DoIt() ActiveWorkbook.Worksheets("SortIt").Sort.SortField s.Clear ActiveWorkbook.Worksheets("SortIt").Sort.SortField s.Add Key:=Range(ActiveCell.EntireColumn.Cells(1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("SortIt").Sort .SetRange Range(Selection.EntireColumn) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub |
#2
|
|||
|
|||
This will do it, but it is a disgusting Macro.
Start in Cell A1. You'll need to change the book1 part to suit your spreadsheet. The CrapMacro() runs the TerribleMacro() 400 times. Sub TerribleMacro() ' ' ActiveCell.Range("A1:A16").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=ActiveCell, _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange ActiveCell.Range("A1:A16") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ActiveCell.Offset(0, 1).Range("A1").Select End Sub Sub CrapMacro() Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" Application.Run "Book1!TerribleMacro" End Sub |
#3
|
|||
|
|||
The VBA boys should be able to put a loop in there to count the number of columns that needs to be sorted, but I'm not that clever.
i = 0 For i <= Count Number of columns with data Sort i++ Something like that. I clicked the "Use Relative References" button (next to "Record Macro") to make it jump left without using hard references. |
#4
|
|||
|
|||
Answer
Thank you. Your code snippets got me past my issues with the syntax. The following works.
Code:
Sub DoIt() ' Before running macro, select first (leftmost) column to sort. Dim Counter As Integer Counter = 1 Do While Counter <= 280 'set number according to columns remaining to sort ActiveWorkbook.Worksheets("SortIt").Sort.SortFields.Clear ActiveWorkbook.Worksheets("SortIt").Sort.SortFields.Add Key:=ActiveCell.EntireColumn.Cells(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("SortIt").Sort .SetRange Selection.EntireColumn .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ActiveCell.Offset(0, 1).Range("A1").Select Counter = Counter + 1 Loop End Sub |
#5
|
||||
|
||||
Code:
Sub blah() With ActiveWorkbook.Worksheets("SortIt") For colm = 1 To 340 .Cells(1, colm).Resize(16).Sort .Cells(1, colm) Next colm End With End Sub 340 columns starting at column A Sort range in each column is from row 1 to 16. (If your range is from row 3 to 10 then change to: .Cells(3, colm).Resize(8).Sort .Cells(1, colm) You do NOT need to change the second 1. There are no headers in each column. An ascending sort is required. The above are the defaults - it is very easy to include/tweak these. Come back if necessary. |
Tags |
macro, sorting columns |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
.pdf to .xlsx - How to get sort-able columns? | CCinPA | Excel | 0 | 07-05-2016 08:30 AM |
Normal sort not bringing along other columns | Dave Fraser | Excel | 2 | 06-06-2014 11:48 AM |
Need help sorting data in columns that will sort by number of Xs | brenna.at.work | Excel | 8 | 04-12-2014 07:00 AM |
quick replace, sort, change columns | userman | Excel | 1 | 05-01-2012 06:24 AM |
How to sort table having three columns? | Bahir Barak | Word | 2 | 01-20-2011 01:52 PM |