can I sort using column headings, in a macro, not "a1"

[Guest]

I have written a macro which includes a sort.


Problem happens when I delete a column and then run the macro. The macro
automatically sorts on original column.

Can I use the column heading ie SURNAME to sort. This would be the same
heading after I delete a column, thus solve my problem.

Many thanks

John

 

[Frank Kabel]

Hi
in your code (which unfortunately you haven't posted) you may for
example use
- the find method to locate the column
- application.match to locate it

 

[Tom Ogilvy]

Not directly unless this is also a defined name (insert=>Name=>Define)

assume headers in row 1

Dim res as Variant
Dim rng as Range

res = Application.Match("Surname",rows(1),0)
if not iserror(res) then
set rng = rows(1).Cells(1,res)
else
msgbox "Not found"
Exit sub
end if

Range("A1").CurrentRegion.Sort Key1:=rng

 

[Guest]

Frank,

This is my code:

Range("A1:AE440").Select

Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub


Problem happens if I delete a column A - F, which I need to do. The macro
will always sort column G, not 'SURNAME' which is in G1.

Once again, many thanks

John

 

[Tom Ogilvy]

use my code and change

Selection.Sort Key1:=rng,