Macro To Sort By Column Header Names

[Derek Hart]

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]

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.

 

[Mike Rogers]

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]

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]

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]

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]

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.

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