Sorting Range Help

H

HelpMe

I Have a problem i am not sure how to solve. I have created a Sort
option seen below my problem is that i do not want to always stop in
N24. Is there a way to solve this? Thanks in advance.

Sub Sorty()

Range("A12:N24").Select
Selection.Sort Key1:=Range("D12"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A12:B12").Select
Selection.AutoFill Destination:=Range("A12:B23"),
Type:=xlFillDefault
Range("A12:B23").Select
End Sub
 
N

Nick Hodge

There is seldom any need for all the 'Select' you have as you can operate on
the range without selecting, but to simply cure your issue modify the code
as shown

Sub Sorty()
Dim lLastRow as Long
lLastRow=Range("N65536").End(xlUp).Row
Range("A12:"&lLastRow).Select
Selection.Sort Key1:=Range("D12"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A12:B12").Select
Selection.AutoFill Destination:=Range("A12:B23"),
Type:=xlFillDefault
Range("A12:B23").Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
H

HelpMe

Thanks for the help but I am getting an error and not sure how to solv
it!:confused: It Says *Method 'Range' of object '_Global' failed*
Here is the code I am using.

Sub Sorty()
Dim lLastRow As Long
lLastRow = Range("M65536").End(xlUp).Row
Range("B12:" & lLastRow).Select
Selection.Sort Key1:=Range("D12"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Thanks for any help
 
C

Chip Pearson

The line of code
Range("B12:" & lLastRow).Select
should be
Range("B12:M" & lLastRow).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
N

Nick Hodge

Oops

That's what comes of trying to be clever and not testing...sorry! Thanks
for bailing me out Chip ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]


Chip Pearson said:
The line of code
Range("B12:" & lLastRow).Select
should be
Range("B12:M" & lLastRow).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Top