Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
Joel,
It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up
the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
It will find the data I don't need to sort. My data is in this "general" form:
Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
Then you need to use xldown instead of xlup. the code search column J for
last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
In my "general" form it is blank rows.
-- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below. Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes end with End Sub "sutibusan" wrote: In my "general" form it is blank rows. -- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
I have rows 1 & 2 as headers & row 2 gets pasted to row 62.
If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was blank) & I'm not getting my sort by rows. -- sutibusan "Joel" wrote: The code should work as is if the sheet is the active sheet. If you want to improve the code then include the sheet name as shown below. Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes end with End Sub "sutibusan" wrote: In my "general" form it is blank rows. -- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
With a sort the header row is only one row. You need to make the header
option xlNo and start the sort at row 3 Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlNo end with End Sub "sutibusan" wrote: I have rows 1 & 2 as headers & row 2 gets pasted to row 62. If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was blank) & I'm not getting my sort by rows. -- sutibusan "Joel" wrote: The code should work as is if the sheet is the active sheet. If you want to improve the code then include the sheet name as shown below. Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes end with End Sub "sutibusan" wrote: In my "general" form it is blank rows. -- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
I get the sort done right, except the original row 1 & 2 are now between the
rows that have data in column J & those that have no data in column J. Heres's my sub: Sub SortJtop() 'Dim Blank As Double' I don't need this since Blank is not used anywhere With Sheets("EXPIDITE.RPT") Lastrow = .Range("J1").End(xlDown).Row Set SortRange = .Range("J3:J" & Lastrow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:=xlNo End With End Sub -- sutibusan "Joel" wrote: With a sort the header row is only one row. You need to make the header option xlNo and start the sort at row 3 Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlNo end with End Sub "sutibusan" wrote: I have rows 1 & 2 as headers & row 2 gets pasted to row 62. If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was blank) & I'm not getting my sort by rows. -- sutibusan "Joel" wrote: The code should work as is if the sheet is the active sheet. If you want to improve the code then include the sheet name as shown below. Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes end with End Sub "sutibusan" wrote: In my "general" form it is blank rows. -- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
what you are saying doesn't make sense unless the old code moved the two
header rows. try manually putting the headers back in there original locations and try the code again. Also you can try the following from Lastrow = .Range("J1").End(xlDown).Row to Lastrow = .Range("J3").End(xlDown).Row "sutibusan" wrote: I get the sort done right, except the original row 1 & 2 are now between the rows that have data in column J & those that have no data in column J. Heres's my sub: Sub SortJtop() 'Dim Blank As Double' I don't need this since Blank is not used anywhere With Sheets("EXPIDITE.RPT") Lastrow = .Range("J1").End(xlDown).Row Set SortRange = .Range("J3:J" & Lastrow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:=xlNo End With End Sub -- sutibusan "Joel" wrote: With a sort the header row is only one row. You need to make the header option xlNo and start the sort at row 3 Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlNo end with End Sub "sutibusan" wrote: I have rows 1 & 2 as headers & row 2 gets pasted to row 62. If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was blank) & I'm not getting my sort by rows. -- sutibusan "Joel" wrote: The code should work as is if the sheet is the active sheet. If you want to improve the code then include the sheet name as shown below. Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes end with End Sub "sutibusan" wrote: In my "general" form it is blank rows. -- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a list, then sort #2
I reload the file to be sorted each time before running the macro. I use the
version you've given me each time. With J1 in the Lastrow function, it sorts the top 2 rows into the middle of the data, but with J3 in the function, it only moves the data from J5 to J4. At least, that's all I see change. I've been expecting your code to do the trick for me, but, I just tried a different way by recording another macro: move the 2 header rows to Sheet1, go back to the main worksheet, run the sort you gave me & it does the trick (with J1 in the Lastrow function). Since I moved the header rows one way, it'll be a snap to move them back. It's stuff like this that really makes me want to slap Bill Gates! Thank you for your help, Joel -- sutibusan "Joel" wrote: what you are saying doesn't make sense unless the old code moved the two header rows. try manually putting the headers back in there original locations and try the code again. Also you can try the following from Lastrow = .Range("J1").End(xlDown).Row to Lastrow = .Range("J3").End(xlDown).Row "sutibusan" wrote: I get the sort done right, except the original row 1 & 2 are now between the rows that have data in column J & those that have no data in column J. Heres's my sub: Sub SortJtop() 'Dim Blank As Double' I don't need this since Blank is not used anywhere With Sheets("EXPIDITE.RPT") Lastrow = .Range("J1").End(xlDown).Row Set SortRange = .Range("J3:J" & Lastrow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:=xlNo End With End Sub -- sutibusan "Joel" wrote: With a sort the header row is only one row. You need to make the header option xlNo and start the sort at row 3 Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlNo end with End Sub "sutibusan" wrote: I have rows 1 & 2 as headers & row 2 gets pasted to row 62. If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was blank) & I'm not getting my sort by rows. -- sutibusan "Joel" wrote: The code should work as is if the sheet is the active sheet. If you want to improve the code then include the sheet name as shown below. Sub SortJtop() Dim Blank As Double with sheets("General") LastRow = .Range("J1").End(xldown).Row Set SortRange = .Range("J3:J" & LastRow) SortRange.Sort _ Key1:=.Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes end with End Sub "sutibusan" wrote: In my "general" form it is blank rows. -- sutibusan "Joel" wrote: Then you need to use xldown instead of xlup. the code search column J for last row before Blank Row. Is Zip1 certain data or just a blnk Row? Sub SortJtop() Dim Blank As Double LastRow = Range("J1").End(xldown).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: It will find the data I don't need to sort. My data is in this "general" form: Data1 Data2 Data3 Zip1 (blank rows) Data5 Data4 Data6 What I need to sort is Data1 to Data3 (based on column J), because my last sort left me at Zip1. What I haven't been able to figure our is how to catch is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row where Zip1 is will change daily. -- sutibusan "Joel" wrote: The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up the column until it finds a cell with data. Sub SortJtop() Dim Blank As Double LastRow = Range("J" & Rows.Count).End(xlup).Row Set SortRange = Range("J3:J" & LastRow) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: Joel, It seems to be a problem of catching where the end of my sort range is (my 'x'). I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a 'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)', would catch my starting/ending point, but that doesn't do it either. -- sutibusan "Joel" wrote: Sub SortJtop() Dim Blank As Double Set SortRange = Range("J3:J" & x) SortRange.Sort _ Key1:=Range("J3"), _ Order1:=xlAscending, _ Header:= xlYes End Sub "sutibusan" wrote: I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want sort a list but the sort is unlit on the home tab how do I | Excel Worksheet Functions | |||
Select a list, then sort | Excel Programming | |||
how to randomly select a name in a list and then the select the ne | Excel Worksheet Functions | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Select and then sort a list | Excel Programming |