Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
If I want to sort by 4 columns, I know I can do that by hand, but I wish to
do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
If you do this manually, you have to sort the data twice because of the limit of
3 keys in sorting. Same thing in code. You have to do two sorts. The easiest way to get code that works the way you want is to record a macro when you do those two sorts. It would be a guess for us to know what 4 columns you want and what order to sort each column. Derek Hart wrote: If I want to sort by 4 columns, I know I can do that by hand, but I wish to do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
Derek
This looks like something you can do yourself. Record a macro while doing the sort the way you want it. Assign it to a button or keyboard short cut and you are done!!! Give it a try!!! Mike Rogers "Derek Hart" wrote: If I want to sort by 4 columns, I know I can do that by hand, but I wish to do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
Thank you for the response. What I was looking for was code to do this,
with examples as column names. I do know the column names that the sort will be on. I wanted to see how this could be done in VBA, using specific column names (referring to row 1). It would sort on Event, Key, State, City, for example. "Dave Peterson" wrote in message ... If you do this manually, you have to sort the data twice because of the limit of 3 keys in sorting. Same thing in code. You have to do two sorts. The easiest way to get code that works the way you want is to record a macro when you do those two sorts. It would be a guess for us to know what 4 columns you want and what order to sort each column. Derek Hart wrote: If I want to sort by 4 columns, I know I can do that by hand, but I wish to do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
This assumes the data starts with headers in row 1. All the used columns have
headers in row 1. It assumes that the last row of the range to sort has data in column A. Option Explicit Sub testme01() Dim wks As Worksheet Dim myKeys As Variant Dim FoundCell As Range Dim iCtr As Long Dim RngToSort As Range Dim LastRow As Long Dim LastCol As Long 'put these in the correct order 'Event is the primary key 'Key is the secondary key 'State is the tertiary key 'City is the quaternary key '(I visited: http://en.wiktionary.org/wiki/tertiary for that 4th one!) myKeys = Array("Event", "Key", "State", "City") Set wks = Worksheets("sheet1") With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set RngToSort = .Range("a1", .Cells(LastRow, LastCol)) For iCtr = UBound(myKeys) To LBound(myKeys) Step -1 With .Rows(1) Set FoundCell = .Cells.Find(what:=myKeys(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then MsgBox myKeys(iCtr) & " wasn't found in row 1!" _ & vbLf & "Sort may not be correct!" _ & "That key is skipped!" Else RngToSort.Sort key1:=.Columns(FoundCell.Column), _ order1:=xlAscending, _ header:=xlYes End If Next iCtr End With End Sub Derek Hart wrote: Thank you for the response. What I was looking for was code to do this, with examples as column names. I do know the column names that the sort will be on. I wanted to see how this could be done in VBA, using specific column names (referring to row 1). It would sort on Event, Key, State, City, for example. "Dave Peterson" wrote in message ... If you do this manually, you have to sort the data twice because of the limit of 3 keys in sorting. Same thing in code. You have to do two sorts. The easiest way to get code that works the way you want is to record a macro when you do those two sorts. It would be a guess for us to know what 4 columns you want and what order to sort each column. Derek Hart wrote: If I want to sort by 4 columns, I know I can do that by hand, but I wish to do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
Some columns may not have data in all rows, so I would like to name the
column to specifically look for to get the row count. So in this line: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Is there any way to replace the "A" with the name of the column to look at? Derek "Dave Peterson" wrote in message ... This assumes the data starts with headers in row 1. All the used columns have headers in row 1. It assumes that the last row of the range to sort has data in column A. Option Explicit Sub testme01() Dim wks As Worksheet Dim myKeys As Variant Dim FoundCell As Range Dim iCtr As Long Dim RngToSort As Range Dim LastRow As Long Dim LastCol As Long 'put these in the correct order 'Event is the primary key 'Key is the secondary key 'State is the tertiary key 'City is the quaternary key '(I visited: http://en.wiktionary.org/wiki/tertiary for that 4th one!) myKeys = Array("Event", "Key", "State", "City") Set wks = Worksheets("sheet1") With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set RngToSort = .Range("a1", .Cells(LastRow, LastCol)) For iCtr = UBound(myKeys) To LBound(myKeys) Step -1 With .Rows(1) Set FoundCell = .Cells.Find(what:=myKeys(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then MsgBox myKeys(iCtr) & " wasn't found in row 1!" _ & vbLf & "Sort may not be correct!" _ & "That key is skipped!" Else RngToSort.Sort key1:=.Columns(FoundCell.Column), _ order1:=xlAscending, _ header:=xlYes End If Next iCtr End With End Sub Derek Hart wrote: Thank you for the response. What I was looking for was code to do this, with examples as column names. I do know the column names that the sort will be on. I wanted to see how this could be done in VBA, using specific column names (referring to row 1). It would sort on Event, Key, State, City, for example. "Dave Peterson" wrote in message ... If you do this manually, you have to sort the data twice because of the limit of 3 keys in sorting. Same thing in code. You have to do two sorts. The easiest way to get code that works the way you want is to record a macro when you do those two sorts. It would be a guess for us to know what 4 columns you want and what order to sort each column. Derek Hart wrote: If I want to sort by 4 columns, I know I can do that by hand, but I wish to do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro To Sort By Column Header Names
How are you naming the column?
Are you using Insert|Name|Define? Or are you "naming" it by just giving it a unique header value in the first row? If you're using insert|Name|define: lastrow = .cells(.rows.count, .range("columnnamehere").column).end(xlup).row If you're using a unique header, you can search for that string: dim myColHeader as string dim wks as worksheet dim myColCell as range myColHeader = "someuniquestringhere" with wks 'declared nicely and set nicely! with .rows(1) Set mycolCell = .Cells.Find(what:=mycolheader, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) end with end with if mycolcell is nothing then msgbox "that header wasn't found!" exit sub end if ....then later... LastRow = .Cells(.Rows.Count, mycolcell.column).End(xlUp).Row ======== Untested, uncompiled. Watch for typos. Derek Hart wrote: Some columns may not have data in all rows, so I would like to name the column to specifically look for to get the row count. So in this line: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Is there any way to replace the "A" with the name of the column to look at? Derek "Dave Peterson" wrote in message ... This assumes the data starts with headers in row 1. All the used columns have headers in row 1. It assumes that the last row of the range to sort has data in column A. Option Explicit Sub testme01() Dim wks As Worksheet Dim myKeys As Variant Dim FoundCell As Range Dim iCtr As Long Dim RngToSort As Range Dim LastRow As Long Dim LastCol As Long 'put these in the correct order 'Event is the primary key 'Key is the secondary key 'State is the tertiary key 'City is the quaternary key '(I visited: http://en.wiktionary.org/wiki/tertiary for that 4th one!) myKeys = Array("Event", "Key", "State", "City") Set wks = Worksheets("sheet1") With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set RngToSort = .Range("a1", .Cells(LastRow, LastCol)) For iCtr = UBound(myKeys) To LBound(myKeys) Step -1 With .Rows(1) Set FoundCell = .Cells.Find(what:=myKeys(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then MsgBox myKeys(iCtr) & " wasn't found in row 1!" _ & vbLf & "Sort may not be correct!" _ & "That key is skipped!" Else RngToSort.Sort key1:=.Columns(FoundCell.Column), _ order1:=xlAscending, _ header:=xlYes End If Next iCtr End With End Sub Derek Hart wrote: Thank you for the response. What I was looking for was code to do this, with examples as column names. I do know the column names that the sort will be on. I wanted to see how this could be done in VBA, using specific column names (referring to row 1). It would sort on Event, Key, State, City, for example. "Dave Peterson" wrote in message ... If you do this manually, you have to sort the data twice because of the limit of 3 keys in sorting. Same thing in code. You have to do two sorts. The easiest way to get code that works the way you want is to record a macro when you do those two sorts. It would be a guess for us to know what 4 columns you want and what order to sort each column. Derek Hart wrote: If I want to sort by 4 columns, I know I can do that by hand, but I wish to do this in a macro, and use the column headers (row 1 always) to name the columns to sort by. Possible to do in a macro. Example? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort a worksheet without including the column header row | Excel Discussion (Misc queries) | |||
can I sort names in a column by the last name if it is typed last | Excel Worksheet Functions | |||
copy column and header names from existing spreadsheet to new spre | New Users to Excel | |||
sort column header | Excel Worksheet Functions | |||
Using Macro to Sort Names, Numbers & addresses | Excel Worksheet Functions |