Sorting Range Help

  • Thread starter Thread starter HelpMe
  • Start date Start date
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
 
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
(e-mail address removed)
 
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
 
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
 
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
(e-mail address removed)


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...
 
Back
Top