finding last row in a fixed section then sort
 

I have a worksheet that has a fixed area cells a8:n40 this area will not be
filled and will contain blank rows. i am doing a sort in ascending order
first and then i want to select the rows that contain data and not blank rows
to do a second sort in descending order. Here is what i have already for the
second sort and it will error when placed in operation.

this first line works
worksheets("HD").Range("A8:n40").Sort Key1:=Worksheets("HD").Range("m8"),
Order1:=xlAscending 'xlAscending xlDescending

from this point it will not select the data and errors out

Dim LastRow As Integer 'This is the LAST Non Empty Row

LastRow = Worksheets("HD").UsedRange.Row - 1 + _
Worksheets("HD").UsedRange.Rows.Count

Worksheets("HD").Range("A8:0" & LastRow & ").Sort
Key1:=Worksheets("HD").Range("m8"), Order1:=xlDescending 'xlAscending
xlDescending

finding last row in a fixed section then sort
 

You don't mention what the error message is, but you appear to have a lone
double quote in this line:
Worksheets("HD").Range("A8:0" & LastRow & ").
You should not need the & " after Lastrow

--
Kevin Vaughn


"toolman" wrote:

I have a worksheet that has a fixed area cells a8:n40 this area will not be
filled and will contain blank rows. i am doing a sort in ascending order
first and then i want to select the rows that contain data and not blank rows
to do a second sort in descending order. Here is what i have already for the
second sort and it will error when placed in operation.

this first line works
worksheets("HD").Range("A8:n40").Sort Key1:=Worksheets("HD").Range("m8"),
Order1:=xlAscending 'xlAscending xlDescending

from this point it will not select the data and errors out

Dim LastRow As Integer 'This is the LAST Non Empty Row

LastRow = Worksheets("HD").UsedRange.Row - 1 + _
Worksheets("HD").UsedRange.Rows.Count

Worksheets("HD").Range("A8:0" & LastRow & ").Sort
Key1:=Worksheets("HD").Range("m8"), Order1:=xlDescending 'xlAscending
xlDescending

finding last row in a fixed section then sort
 

the error message is

1004 Application-defined or object-defined error

i made the corrections and the error is still there.

"Kevin Vaughn" wrote:

You don't mention what the error message is, but you appear to have a lone
double quote in this line:
Worksheets("HD").Range("A8:0" & LastRow & ").
You should not need the & " after Lastrow

--
Kevin Vaughn


"toolman" wrote:

I have a worksheet that has a fixed area cells a8:n40 this area will not be
filled and will contain blank rows. i am doing a sort in ascending order
first and then i want to select the rows that contain data and not blank rows
to do a second sort in descending order. Here is what i have already for the
second sort and it will error when placed in operation.

this first line works
worksheets("HD").Range("A8:n40").Sort Key1:=Worksheets("HD").Range("m8"),
Order1:=xlAscending 'xlAscending xlDescending

from this point it will not select the data and errors out

Dim LastRow As Integer 'This is the LAST Non Empty Row

LastRow = Worksheets("HD").UsedRange.Row - 1 + _
Worksheets("HD").UsedRange.Rows.Count

Worksheets("HD").Range("A8:0" & LastRow & ").Sort
Key1:=Worksheets("HD").Range("m8"), Order1:=xlDescending 'xlAscending
xlDescending

finding last row in a fixed section then sort
 

I just pasted what I originally took as an O to my spreadsheet and compared
it with a 0. I would say that is your problem
Worksheets("HD").Range("A8:0" & LastRow & ").Sort
the a8:0 should be a8:O (or some other column letter)


--
Kevin Vaughn


"toolman" wrote:

I have a worksheet that has a fixed area cells a8:n40 this area will not be
filled and will contain blank rows. i am doing a sort in ascending order
first and then i want to select the rows that contain data and not blank rows
to do a second sort in descending order. Here is what i have already for the
second sort and it will error when placed in operation.

this first line works
worksheets("HD").Range("A8:n40").Sort Key1:=Worksheets("HD").Range("m8"),
Order1:=xlAscending 'xlAscending xlDescending

from this point it will not select the data and errors out

Dim LastRow As Integer 'This is the LAST Non Empty Row

LastRow = Worksheets("HD").UsedRange.Row - 1 + _
Worksheets("HD").UsedRange.Rows.Count

Worksheets("HD").Range("A8:0" & LastRow & ").Sort
Key1:=Worksheets("HD").Range("m8"), Order1:=xlDescending 'xlAscending
xlDescending

finding last row in a fixed section then sort
 

Kevin

Thanks for the extra set of eyes
The sort works now but it still includes the rows that do not have data.

"Kevin Vaughn" wrote:

I just pasted what I originally took as an O to my spreadsheet and compared
it with a 0. I would say that is your problem
Worksheets("HD").Range("A8:0" & LastRow & ").Sort
the a8:0 should be a8:O (or some other column letter)


--
Kevin Vaughn


"toolman" wrote:

I have a worksheet that has a fixed area cells a8:n40 this area will not be
filled and will contain blank rows. i am doing a sort in ascending order
first and then i want to select the rows that contain data and not blank rows
to do a second sort in descending order. Here is what i have already for the
second sort and it will error when placed in operation.

this first line works
worksheets("HD").Range("A8:n40").Sort Key1:=Worksheets("HD").Range("m8"),
Order1:=xlAscending 'xlAscending xlDescending

from this point it will not select the data and errors out

Dim LastRow As Integer 'This is the LAST Non Empty Row

LastRow = Worksheets("HD").UsedRange.Row - 1 + _
Worksheets("HD").UsedRange.Rows.Count

Worksheets("HD").Range("A8:0" & LastRow & ").Sort
Key1:=Worksheets("HD").Range("m8"), Order1:=xlDescending 'xlAscending
xlDescending

finding last row in a fixed section then sort
 

I don't know if this is your problem, but I do notice that you are using
usedrange. There are times when usedrange is not correct. From Excel, try
doing cntl-end and see if it takes you past rows/columns that have data. If
so, try highlighting, for instance the extra rows, and right-click and choose
delete entire-rows. Then save, and see if a cntl-end now takes you to the
true end of your data.
--
Kevin Vaughn


"toolman" wrote:

Kevin

Thanks for the extra set of eyes
The sort works now but it still includes the rows that do not have data.

"Kevin Vaughn" wrote:

I just pasted what I originally took as an O to my spreadsheet and compared
it with a 0. I would say that is your problem
Worksheets("HD").Range("A8:0" & LastRow & ").Sort
the a8:0 should be a8:O (or some other column letter)


--
Kevin Vaughn


"toolman" wrote:

I have a worksheet that has a fixed area cells a8:n40 this area will not be
filled and will contain blank rows. i am doing a sort in ascending order
first and then i want to select the rows that contain data and not blank rows
to do a second sort in descending order. Here is what i have already for the
second sort and it will error when placed in operation.

this first line works
worksheets("HD").Range("A8:n40").Sort Key1:=Worksheets("HD").Range("m8"),
Order1:=xlAscending 'xlAscending xlDescending

from this point it will not select the data and errors out

Dim LastRow As Integer 'This is the LAST Non Empty Row

LastRow = Worksheets("HD").UsedRange.Row - 1 + _
Worksheets("HD").UsedRange.Rows.Count

Worksheets("HD").Range("A8:0" & LastRow & ").Sort
Key1:=Worksheets("HD").Range("m8"), Order1:=xlDescending 'xlAscending
xlDescending