Varing range

[shawnews]

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

 

[merjet]

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

 

[shawnews]

Tks, I'll try your suggestion.

Roger

 

[Steven Bentley]

lastrow = range("j65225").end(xlup).row
range("J" & lastrow & :"a23").copy destination:=sheets(- "yoursheetname-" or number-).range("a1").paste


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