Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
sort by section number | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |