Determine last row then sort
 

I'm trying to determine the last row of data, then move up
a number of rows (to avoid sorting formula cells) and then
perform a sort. I'm not sure how to move up x number of
rows and then use that as the ending portion for a sort.
The first part of the macro shows moving up 15 rows to
avoid the formulas. The second part is hardcoding the
range value. Here is what I have so far

Dim shtworksheet As Worksheet
Dim rngData As Range
Set shtworksheet = _
Application.Workbooks("test1.xls").Worksheets("she et1")
'Initialize the variables
Set rngData = shtworksheet.Range("B6").CurrentRegion
'last cell (moves the cellpointer to the lower right
corner of active files active area
Cells.SpecialCells(xlCellTypeLastCell).Select
'Move up fifteen rows
Selection.Offset(-15, 0).Select
'Identify the specific range for sorting
Range("B6:T50").Select
'sort data by purchase date
'Header:=xlYes leaves the first row untouched
'Header:=xlGuess ??
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess

Thanks for your help,
Bernice

Determine last row then sort
 

Bernice

Dim LastRw as Long

LastRw = Cells.SpecialCells(xlCellTypeLastCell).Offset(-15,0).Row

Range("B6:T" & LastRw).Sort etc...

The LastCell is not always 100% accurate. You may be better off doing
something like this

LastRw = Range("A65536").End(xlUp).Offset(-15,0).Row


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Bernice" wrote in message
...
I'm trying to determine the last row of data, then move up
a number of rows (to avoid sorting formula cells) and then
perform a sort. I'm not sure how to move up x number of
rows and then use that as the ending portion for a sort.
The first part of the macro shows moving up 15 rows to
avoid the formulas. The second part is hardcoding the
range value. Here is what I have so far

Dim shtworksheet As Worksheet
Dim rngData As Range
Set shtworksheet = _
Application.Workbooks("test1.xls").Worksheets("she et1")
'Initialize the variables
Set rngData = shtworksheet.Range("B6").CurrentRegion
'last cell (moves the cellpointer to the lower right
corner of active files active area
Cells.SpecialCells(xlCellTypeLastCell).Select
'Move up fifteen rows
Selection.Offset(-15, 0).Select
'Identify the specific range for sorting
Range("B6:T50").Select
'sort data by purchase date
'Header:=xlYes leaves the first row untouched
'Header:=xlGuess ??
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess

Thanks for your help,
Bernice

Determine last row then sort
 

Dick,

Thanks that worked well.

Bernice
-----Original Message-----
Bernice

Dim LastRw as Long

LastRw = Cells.SpecialCells(xlCellTypeLastCell).Offset(-
15,0).Row

Range("B6:T" & LastRw).Sort etc...

The LastCell is not always 100% accurate. You may be
better off doing
something like this

LastRw = Range("A65536").End(xlUp).Offset(-15,0).Row


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Bernice" wrote in
message
...
I'm trying to determine the last row of data, then move
up
a number of rows (to avoid sorting formula cells) and
then
perform a sort. I'm not sure how to move up x number of
rows and then use that as the ending portion for a sort.
The first part of the macro shows moving up 15 rows to
avoid the formulas. The second part is hardcoding the
range value. Here is what I have so far

Dim shtworksheet As Worksheet
Dim rngData As Range
Set shtworksheet = _
Application.Workbooks("test1.xls").Worksheets
("sheet1")
'Initialize the variables
Set rngData = shtworksheet.Range("B6").CurrentRegion
'last cell (moves the cellpointer to the lower right
corner of active files active area
Cells.SpecialCells(xlCellTypeLastCell).Select
'Move up fifteen rows
Selection.Offset(-15, 0).Select
'Identify the specific range for sorting
Range("B6:T50").Select
'sort data by purchase date
'Header:=xlYes leaves the first row untouched
'Header:=xlGuess ??
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess

Thanks for your help,
Bernice


.