Hi.
Excel 2007
I have a range of cells containing text data, listed under several headings.
I need to be able to select the individual group of headings and associated
data in the adjoining columns and sort it A - Z The problem is that although
the number of of columns is constant the number of rows is Variable and
increases as new data is added.
I am having trouble with the syntax of the macro to select Multiple rows and
columns.

Regards Keith B

Lets see if we cannot clear up some of your confusion. The code below will
use the currently selected cell and use it as the column to use as the 'key'
in an A-Z sequence. This code will also work in some earlier versions of
Excel (as 2003) while code from a recorded macro in 2007 won't. Hopefully my
comments will provide the insight needed for you to adapt it to your specific
needs, if not, just ask questions and we'll try to answer them.

A couple of special points: first, remember that a Range can be a single
cell or a number of them.

Sub SortOnCurrentColumn()
Const firstColToSort = "A"
Const lastColToSort = "P"
Const firstRowToSort = 2 ' labels are in 2
Dim sortRange As Range
Dim sortKey As Range
Dim lastRowToSort As Long

'verify that the current selection is
'within the columns to be sorted
If ActiveCell.Column < Range(firstColToSort & 1).Column Or _
ActiveCell.Column Range(lastColToSort & 1).Column Then
MsgBox "Selected cell is not within the sort area"
Exit Sub ' not within the area to be sorted, quit
End If
'also make sure that the current selection does not
'contain more than one column - that would only
'confuse us more
If Selection.Columns.Count 1 Then
MsgBox "Cannot sort with multiple columns selected."
Exit Sub
End If
'determine the last row to sort
'assumes all columns always have data down to last row
lastRowToSort = Range(firstColToSort & Rows.Count). _
End(xlUp).Row
'set up the range to be sorted
Set sortRange = Range(firstColToSort & firstRowToSort & ":" _
& lastColToSort & lastRowToSort)
'set the sort key using .Cells
'add 1 to first row if first row contains labels
Set sortKey = Cells(firstRowToSort + 1, ActiveCell.Column)
sortRange.Sort Key1:=sortKey, Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'some housekeeping
Set sortKey = Nothing
Set sortRange = Nothing
End Sub

"Keith B" wrote:

Hi.
Excel 2007
I have a range of cells containing text data, listed under several headings.
I need to be able to select the individual group of headings and associated
data in the adjoining columns and sort it A - Z The problem is that although
the number of of columns is constant the number of rows is Variable and
increases as new data is added.
I am having trouble with the syntax of the macro to select Multiple rows and
columns.

Regards Keith B