I am trying to select a variable range, beginning in column A and ending in
the furtherest column to the right being used AND last row used.

There are three factors to remember:
1) The actual beginning point (upper leftmost cell) may change as I
insert/delete
rows above the data to be sorted. The first cell in column A for the
sort range
is located below the header FREQ.
2) The number of rows down will increase as data is added.
3) The number of columns used may increase if I determine there are other
items I need to calculate/input.

Currently, I am using columns A-R. The column headers are on row 8, which
may increase/decrease based on item #1 above.

One other quick question:
I believe I have the sort code I need. My question is this: After the
range is selected, I believe the first part of the sort code is
"Selection:Sort" (without quotes). Is this correct?

Thanks for your help!

Les

Hi,

Try this:
Sub Macro1()
' Macro1 Macro
' Macro recorded 1/24/2009
Range("A1").Select
Cells.Find(What:="FREQ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
TopCell = ActiveCell.Address
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range(TopCell), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range(TopCell).Select
End Sub

David
"WLMPilot" wrote:

I am trying to select a variable range, beginning in column A and ending in
the furtherest column to the right being used AND last row used.

There are three factors to remember:
1) The actual beginning point (upper leftmost cell) may change as I
insert/delete
rows above the data to be sorted. The first cell in column A for the
sort range
is located below the header FREQ.
2) The number of rows down will increase as data is added.
3) The number of columns used may increase if I determine there are other
items I need to calculate/input.

Currently, I am using columns A-R. The column headers are on row 8, which
may increase/decrease based on item #1 above.

One other quick question:
I believe I have the sort code I need. My question is this: After the
range is selected, I believe the first part of the sort code is
"Selection:Sort" (without quotes). Is this correct?

Thanks for your help!

Les

I think using CurrentRegion in your code will cause a problem in the sort if
there are any blank rows or blank columns in the data area.

--
Rick (MVP - Excel)


"David" wrote in message
...
Hi,

Try this:
Sub Macro1()
' Macro1 Macro
' Macro recorded 1/24/2009
Range("A1").Select
Cells.Find(What:="FREQ", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
TopCell = ActiveCell.Address
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range(TopCell), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range(TopCell).Select
End Sub

David
"WLMPilot" wrote:

I am trying to select a variable range, beginning in column A and ending
in
the furtherest column to the right being used AND last row used.

There are three factors to remember:
1) The actual beginning point (upper leftmost cell) may change as I
insert/delete
rows above the data to be sorted. The first cell in column A for
the
sort range
is located below the header FREQ.
2) The number of rows down will increase as data is added.
3) The number of columns used may increase if I determine there are
other
items I need to calculate/input.

Currently, I am using columns A-R. The column headers are on row 8,
which
may increase/decrease based on item #1 above.

One other quick question:
I believe I have the sort code I need. My question is this: After the
range is selected, I believe the first part of the sort code is
"Selection:Sort" (without quotes). Is this correct?

Thanks for your help!

Les

This uses cell b2 (cells(2,2) as the sort key. If you want col A use
cells(2,1)

Sub sortarea()
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lc
'MsgBox lr
Range(Cells(1, 1), Cells(lr, lc)).Sort _
Key1:=Cells(2, 2), Order1:=xlAscending, Header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WLMPilot" wrote in message
...
I am trying to select a variable range, beginning in column A and ending in
the furtherest column to the right being used AND last row used.

There are three factors to remember:
1) The actual beginning point (upper leftmost cell) may change as I
insert/delete
rows above the data to be sorted. The first cell in column A for the
sort range
is located below the header FREQ.
2) The number of rows down will increase as data is added.
3) The number of columns used may increase if I determine there are other
items I need to calculate/input.

Currently, I am using columns A-R. The column headers are on row 8, which
may increase/decrease based on item #1 above.

One other quick question:
I believe I have the sort code I need. My question is this: After the
range is selected, I believe the first part of the sort code is
"Selection:Sort" (without quotes). Is this correct?

Thanks for your help!

Les

You don't have to work with selections in your code for most things.

If you do, I think you'll find the code harder to understand and maintain.

You didn't say how you wanted the sort done (or did I miss that???).

Option Explicit
Sub testme()

Dim TopLeftCorner As Range
Dim BotRightCorner As Range

Dim LastRow As Long
Dim LastCol As Long

Dim StringToSearchFor As String
Dim wks As Worksheet

Set wks = ActiveSheet 'worksheets("sheetnamehere")

StringToSearchFor = "Freq"

With wks
With .Range("A1").EntireColumn
Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If TopLeftCorner Is Nothing Then
MsgBox StringToSearchFor & " wasn't found!"
Exit Sub
End If

'using column A to determined the last row to sort
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'using the headers in the row with Freq to determine
'the last column to sort
LastCol _
= .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column

Set BotRightCorner = .Cells(LastRow, LastCol)

With .Range(TopLeftCorner, BotRightCorner)
.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
key3:=.Columns(LastCol), order3:=xlAscending, _
header:=xlYes, MatchCase:=False
End With
End With
End Sub

The sort statement includes the row with Freq, but header:=xlyes means that it
won't be sorted as part of the data.

WLMPilot wrote:

I am trying to select a variable range, beginning in column A and ending in
the furtherest column to the right being used AND last row used.

There are three factors to remember:
1) The actual beginning point (upper leftmost cell) may change as I
insert/delete
rows above the data to be sorted. The first cell in column A for the
sort range
is located below the header FREQ.
2) The number of rows down will increase as data is added.
3) The number of columns used may increase if I determine there are other
items I need to calculate/input.

Currently, I am using columns A-R. The column headers are on row 8, which
may increase/decrease based on item #1 above.

One other quick question:
I believe I have the sort code I need. My question is this: After the
range is selected, I believe the first part of the sort code is
"Selection:Sort" (without quotes). Is this correct?

Thanks for your help!

Les

--

Dave Peterson