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

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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
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

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

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

--
Regards,
Tom Ogilvy


"John" wrote in message
...
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

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

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


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
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

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

use my code and change

Selection.Sort Key1:=rng,

--
Regards,
Tom Ogilvy


"John" wrote in message
...
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


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
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