VBA, excel - find, resize and sort

[Guest]

Hi,

Last week, I posted a similar question and got and answer (thank you) to
resize a range. I got the following macro:

Sub Sort()

Clients '(name of macro to find this range)

With ActiveCell
ilastrow = Cells(Rows.Count, .Column).End(xlUp).Row
Set rng = .Resize(ilastrow - .Row + 1, 9)

Selection.Sort Key1:=Range("B15"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End With

End Sub

The first part of the macro would highlight the range to be sorted and it
works perfectly. However, the second part of the macro doesn't work if I try
to find ANOTHER RANGE to sort. Maybe it's because the range "B15" doesn't
match the cell of the "NEW Range"

Please help and thanks in advance.

 

[Bernie Deitrick]

Danny,

You could change the code from

Selection.Sort Key1:=Range("B15"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

to

rng.Sort Key1:=rng.Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

to always sort based on the second column of the selected range.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,

Thanks for your response. I copied the new formula and when I tried it, it
prompted "syntax error". What will I do.

Thanks again.

 

[Bernie Deitrick]

Danny,

Copy this code:

Sub Sort()

With ActiveCell
ilastrow = Cells(Rows.Count, .Column).End(xlUp).Row
Set rng = .Resize(ilastrow - .Row + 1, 9)

MsgBox rng.Address
rng.Sort Key1:=rng.Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End With

End Sub

Note that there is only one dash or minus sign withing the code (as I pasted
it), here:

ilastrow - .Row

For some reason, the web interface to this newsgroup is putting spurious
characters into code, usually dashes. Take out any extra that appear after
you paste the code into your module, besides the one minus sign.

This code worked well for me, so give it a go. As far as I know, there
isn't anyway around the web interface problem except accessing the newsgroup
with your news reader. If you want to try that, there are instructions
available that I will post, if you want.

HTH,
Bernie
MS Excel MVP