Thank you for your suggestions.

Les

"Don Guillett" wrote:

Now will find "Freq" and sort below that using cell a & row below "Freq"
IF??, instead of finding the last column in row 1 you want the last column
in the Freq row, change
the lc= to Cells(FR, Columns.Count).End(xlToLeft).Column

Sub sortareabelowVariable()
Dim fr, lc, lr As Long
fr = Columns(1).Find("Freq").Row
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(fr, 1), Cells(lr, lc)).Sort _
Key1:=Cells(fr + 1, "a"), Order1:=xlAscending, Header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WLMPilot" wrote in message
...
Wow! Thanks for all the responses. I think I got more coding than is
necessary, but I am not an expert at it by any means. Let me ask it
different way and see what happens.

The only unknown point of the range selection needed is the location of
the
upper left most cell, which is in column A, and the first cell below the
header Freq. I think the bottom and right side intersection can be
determined with xlup and xlleft (or whatever the correct function is).

Below is part of a macro that selects a range then sorts on another
worksheet. I show you this to let you know basically what I am looking
for.
I think that once the upper left corner is located, then a RANGE can be
set
up similar to what is shown below:

Range("A" & begrng & ":Q" & endrng).Select
Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending,
Key2:=Range("A" & begrng) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom

I hope this helps to simplify what I am wanting. Again, thanks for your
suggestions and if what I think is wrong, please let me know and I will go
back and look at the suggestions given.

Thanks,
Les



"Rick Rothstein" wrote:

I screwed up my With..End With blocking. This is the macro I should have
posted...

Sub SortDataBelowFREQ()
Dim FREQrow As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim DataRows As Range
With ActiveSheet
FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _
LookAt:=xlWhole, MatchCase:=True).Row
LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
Set DataRows = .Range(FREQrow & ":" & LastRow)
LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn))
.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

I guess I should point out that I used ActiveSheet, but if you want this
code to apply to a specific sheet, just change the ActiveSheet reference
in
the first With statement to Worksheets("SheetX") where you would replace
SheetX with the worksheet's actual name.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is a macro which uses your Sort code, but which automatically
determines the last used row and last used column, *within* the actual
data area (those rows below the FREQ header row), to apply it to...

Sub SelectDataRows()
Dim FREQrow As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim DataRows As Range
With ActiveSheet
FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _
LookAt:=xlWhole, MatchCase:=True).Row
LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
Set DataRows = .Range(FREQrow & ":" & LastRow)
LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn))
.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
key3:=.Columns(LastCol), order3:=xlAscending, _
header:=xlYes, MatchCase:=False
End With
End Sub

--
Rick (MVP - Excel)


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