sorting a range

[hawk]

Trying to sort a range A5:AZ350 on colum 'H' and then colum 'A' however I
would like to vary the first colum.
I created a macro to do the sort, but would like the macro to ask me what
colum to sort on when it runs , as the first colum varies all the time.
Any help appreciated

Roger

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCol As Range

With Worksheets("sheet1")
.Select 'makes selecting the column easier
Set myRng = .Range("a5:az350")

Set myCol = Nothing
On Error Resume Next
Set myCol = Application.InputBox _
(prompt:="Please click on the column to use as the secondary key", _
Type:=8).Cells(1)
On Error GoTo 0

If myCol Is Nothing Then
'user hit cancel--just quit
Exit Sub
Else
If Intersect(myCol.Cells(1).EntireColumn, myRng) Is Nothing Then
'outside the range to sort, use H again
Set myCol = .Range("H1")
End If
End If

myRng.Sort key1:=.Range("h1"), order1:=xlAscending, _
key2:=myCol, order2:=xlAscending, _
header:=xlYes
End With

End Sub

I guessed at the orders and the headers.