Varing range
I have a macro that selects a range, copys it, then converts the formulas to values, ten sorts the range on colum 'J'
what I need is to now sort the result on colum 'A' and copy to another worksheet. See Below; Sub sort2() ' ' sort2 Macro ' Macro recorded 1/22/2007 by Tablet PC ' Rows("23:677").Select Selection.EntireRow.Hidden = False :Unhide any hiden rows. Application.Goto Reference:="SPEC" : Select range SPEC Selection.Copy : Copy the range SPEC Range("A23").Select : paste the VALUES back Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("J23"), Order1:=xlAscending, Header:=xlGuess, _ :as the range is still highlighted OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ : I now do my first sort on "J" DataOption1:=xlSortNormal :This is where I'm lost I need to find the last cell in Col 'J' with a value, ( this row can change ) and select it to A23 and then sort on Col. 'A' and then copy to another work book. : Any help is appreciated. End Sub Roger |
Varing range
iEnd = Range("J23").End(xlDown).Row
iEnd = Range("J65536").End(xlUp).Row One or both of these should work. (You may need to change the number depending on what cells in your sheet are used.) Then use iEnd in code, e.g. like this: Range("J23:J" & iEnd) Hth, Merjet |
Varing range
Tks, I'll try your suggestion.
Roger "merjet" wrote in message ups.com... iEnd = Range("J23").End(xlDown).Row iEnd = Range("J65536").End(xlUp).Row One or both of these should work. (You may need to change the number depending on what cells in your sheet are used.) Then use iEnd in code, e.g. like this: Range("J23:J" & iEnd) Hth, Merjet |
Varing range
lastrow = range("j65225").end(xlup).row range("J" & lastrow & :"a23").copy destination:=sheets(- "yoursheetname-" or number-).range("a1").paste "shawnews" wrote in message news:5Uwyh.905932$5R2.150080@pd7urf3no... I have a macro that selects a range, copys it, then converts the formulas to values, ten sorts the range on colum 'J' what I need is to now sort the result on colum 'A' and copy to another worksheet. See Below; Sub sort2() ' ' sort2 Macro ' Macro recorded 1/22/2007 by Tablet PC ' Rows("23:677").Select Selection.EntireRow.Hidden = False :Unhide any hiden rows. Application.Goto Reference:="SPEC" : Select range SPEC Selection.Copy : Copy the range SPEC Range("A23").Select : paste the VALUES back Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("J23"), Order1:=xlAscending, Header:=xlGuess, _ :as the range is still highlighted OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ : I now do my first sort on "J" DataOption1:=xlSortNormal :This is where I'm lost I need to find the last cell in Col 'J' with a value, ( this row can change ) and select it to A23 and then sort on Col. 'A' and then copy to another work book. : Any help is appreciated. End Sub Roger |
All times are GMT +1. The time now is 09:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com