Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
how do i save a varing value in a cell like Second? | Setting up and Configuration of Excel | |||
Long and Varing Worksheet Save Times | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |