Unselect range

Biff · 09-30-2005, 03:05 PM

Hi folks!

I have this simple macro created using the recorder:

Sub Macro2()

Application.ScreenUpdating = False
Sheets("Sheet3").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Sheet2").Select
Application.ScreenUpdating = True

End Sub

All it does is sort Sheet3 A:B

I have the macro assigned to a button on another sheet. It works fine except
that after it runs and I go to sheet3 the sorted range, columns A:B, are
still selected.

How can I get the sorted range to be unselected after the macro runs?

Thanks!

Biff

Jim Thomlinson · 09-30-2005, 03:05 PM

Try avoiding the select(s) something like this.

Sub Macro2()

Application.ScreenUpdating = False
Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
Application.ScreenUpdating = True

End Sub

--
HTH...

Jim Thomlinson

"Biff" wrote:

> Hi folks!
>
> I have this simple macro created using the recorder:
>
> Sub Macro2()
>
> Application.ScreenUpdating = False
> Sheets("Sheet3").Select
> Columns("A:B").Select
> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess,
> _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Sheets("Sheet2").Select
> Application.ScreenUpdating = True
>
> End Sub
>
> All it does is sort Sheet3 A:B
>
> I have the macro assigned to a button on another sheet. It works fine except
> that after it runs and I go to sheet3 the sorted range, columns A:B, are
> still selected.
>
> How can I get the sorted range to be unselected after the macro runs?
>
> Thanks!
>
> Biff
>
>
>

Biff · 09-30-2005, 04:05 PM

Hi!

When I tried that I get run-time error 1004:

Sort reference is not valid.

????????

Biff

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Try avoiding the select(s) something like this.
>
> Sub Macro2()
>
> Application.ScreenUpdating = False
> Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> Application.ScreenUpdating = True
>
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Biff" wrote:
>
>> Hi folks!
>>
>> I have this simple macro created using the recorder:
>>
>> Sub Macro2()
>>
>> Application.ScreenUpdating = False
>> Sheets("Sheet3").Select
>> Columns("A:B").Select
>> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
>> Header:=xlGuess,
>> _
>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>> DataOption1:=xlSortNormal
>> Sheets("Sheet2").Select
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> All it does is sort Sheet3 A:B
>>
>> I have the macro assigned to a button on another sheet. It works fine
>> except
>> that after it runs and I go to sheet3 the sorted range, columns A:B, are
>> still selected.
>>
>> How can I get the sorted range to be unselected after the macro runs?
>>
>> Thanks!
>>
>> Biff
>>
>>
>>

Norman Jones · 09-30-2005, 04:05 PM

Hi Jim,

> Sub Macro2()
>
> Application.ScreenUpdating = False
> Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> Application.ScreenUpdating = True
>
> End Sub

I think that there is a subtle problem with this in that, as written, the
sort key refers to the activesheet.

I think you meant something like:

Sub Macro2()
Dim sh As Worksheet

Set sh = ActiveWorkbook.Sheets("Sheet3")

Application.ScreenUpdating = False
sh.Columns("A:B").Sort Key1:=sh.Range("B1"), _
Order1:=xlAscending ', _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True

End Sub

---
Regards,
Norman

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Try avoiding the select(s) something like this.
>
> Sub Macro2()
>
> Application.ScreenUpdating = False
> Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> Application.ScreenUpdating = True
>
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Biff" wrote:
>
>> Hi folks!
>>
>> I have this simple macro created using the recorder:
>>
>> Sub Macro2()
>>
>> Application.ScreenUpdating = False
>> Sheets("Sheet3").Select
>> Columns("A:B").Select
>> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
>> Header:=xlGuess,
>> _
>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>> DataOption1:=xlSortNormal
>> Sheets("Sheet2").Select
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> All it does is sort Sheet3 A:B
>>
>> I have the macro assigned to a button on another sheet. It works fine
>> except
>> that after it runs and I go to sheet3 the sorted range, columns A:B, are
>> still selected.
>>
>> How can I get the sorted range to be unselected after the macro runs?
>>
>> Thanks!
>>
>> Biff
>>
>>
>>

Jim Thomlinson · 09-30-2005, 04:05 PM

Thanks Norman... Sorry Biff... I wasn't explicit with my referencing...
--
HTH...

Jim Thomlinson

"Norman Jones" wrote:

> Hi Jim,
>
> > Sub Macro2()
> >
> > Application.ScreenUpdating = False
> > Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> > Application.ScreenUpdating = True
> >
> > End Sub
>
> I think that there is a subtle problem with this in that, as written, the
> sort key refers to the activesheet.
>
> I think you meant something like:
>
> Sub Macro2()
> Dim sh As Worksheet
>
> Set sh = ActiveWorkbook.Sheets("Sheet3")
>
> Application.ScreenUpdating = False
> sh.Columns("A:B").Sort Key1:=sh.Range("B1"), _
> Order1:=xlAscending ', _
> Header:=xlGuess, OrderCustom:=1, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Application.ScreenUpdating = True
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Try avoiding the select(s) something like this.
> >
> > Sub Macro2()
> >
> > Application.ScreenUpdating = False
> > Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Biff" wrote:
> >
> >> Hi folks!
> >>
> >> I have this simple macro created using the recorder:
> >>
> >> Sub Macro2()
> >>
> >> Application.ScreenUpdating = False
> >> Sheets("Sheet3").Select
> >> Columns("A:B").Select
> >> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
> >> Header:=xlGuess,
> >> _
> >> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> >> DataOption1:=xlSortNormal
> >> Sheets("Sheet2").Select
> >> Application.ScreenUpdating = True
> >>
> >> End Sub
> >>
> >> All it does is sort Sheet3 A:B
> >>
> >> I have the macro assigned to a button on another sheet. It works fine
> >> except
> >> that after it runs and I go to sheet3 the sorted range, columns A:B, are
> >> still selected.
> >>
> >> How can I get the sorted range to be unselected after the macro runs?
> >>
> >> Thanks!
> >>
> >> Biff
> >>
> >>
> >>
>
>
>

Jim Thomlinson · 09-30-2005, 04:05 PM

Just noticed Norman you left Header:= xlGuess ... Probably better to go with
xlYes or xlNo to avoid having Excel make a wrong guess and messing up the
sort... Kinda Picky but it can be important...
--
HTH...

Jim Thomlinson

"Norman Jones" wrote:

> Hi Jim,
>
> > Sub Macro2()
> >
> > Application.ScreenUpdating = False
> > Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> > Application.ScreenUpdating = True
> >
> > End Sub
>
> I think that there is a subtle problem with this in that, as written, the
> sort key refers to the activesheet.
>
> I think you meant something like:
>
> Sub Macro2()
> Dim sh As Worksheet
>
> Set sh = ActiveWorkbook.Sheets("Sheet3")
>
> Application.ScreenUpdating = False
> sh.Columns("A:B").Sort Key1:=sh.Range("B1"), _
> Order1:=xlAscending ', _
> Header:=xlGuess, OrderCustom:=1, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Application.ScreenUpdating = True
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Try avoiding the select(s) something like this.
> >
> > Sub Macro2()
> >
> > Application.ScreenUpdating = False
> > Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Biff" wrote:
> >
> >> Hi folks!
> >>
> >> I have this simple macro created using the recorder:
> >>
> >> Sub Macro2()
> >>
> >> Application.ScreenUpdating = False
> >> Sheets("Sheet3").Select
> >> Columns("A:B").Select
> >> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
> >> Header:=xlGuess,
> >> _
> >> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> >> DataOption1:=xlSortNormal
> >> Sheets("Sheet2").Select
> >> Application.ScreenUpdating = True
> >>
> >> End Sub
> >>
> >> All it does is sort Sheet3 A:B
> >>
> >> I have the macro assigned to a button on another sheet. It works fine
> >> except
> >> that after it runs and I go to sheet3 the sorted range, columns A:B, are
> >> still selected.
> >>
> >> How can I get the sorted range to be unselected after the macro runs?
> >>
> >> Thanks!
> >>
> >> Biff
> >>
> >>
> >>
>
>
>

Tom Ogilvy · 09-30-2005, 05:05 PM

Sub Macro2()

Application.ScreenUpdating = False
With Sheets("Sheet3")
.Columns("A:B").Sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Application.ScreenUpdating = True

End Sub

--
Regards,
Tom Ogilvy

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi folks!
>
> I have this simple macro created using the recorder:
>
> Sub Macro2()
>
> Application.ScreenUpdating = False
> Sheets("Sheet3").Select
> Columns("A:B").Select
> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess,
> _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Sheets("Sheet2").Select
> Application.ScreenUpdating = True
>
> End Sub
>
> All it does is sort Sheet3 A:B
>
> I have the macro assigned to a button on another sheet. It works fine
except
> that after it runs and I go to sheet3 the sorted range, columns A:B, are
> still selected.
>
> How can I get the sorted range to be unselected after the macro runs?
>
> Thanks!
>
> Biff
>
>

Norman Jones · 09-30-2005, 05:05 PM

Hi Jim,

> Just noticed Norman you left Header:= xlGuess ... Probably better to go
> with
> xlYes or xlNo to avoid having Excel make a wrong guess and messing up the
> sort... Kinda Picky but it can be important...

Does this not depend on the data? Given that I did not know if the OP's data
had a header row or not, I deemed it safer to go with the OP's xlGuess. In
the absence of information, it seemed to me that a choice between xlYes and
xlNo would be arbitrary.

---
Regards,
Norman

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Just noticed Norman you left Header:= xlGuess ... Probably better to go
> with
> xlYes or xlNo to avoid having Excel make a wrong guess and messing up the
> sort... Kinda Picky but it can be important...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Norman Jones" wrote:
>
>> Hi Jim,
>>
>> > Sub Macro2()
>> >
>> > Application.ScreenUpdating = False
>> > Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"),
>> > Header:=xlYes,
>> > Application.ScreenUpdating = True
>> >
>> > End Sub
>>
>> I think that there is a subtle problem with this in that, as written, the
>> sort key refers to the activesheet.
>>
>> I think you meant something like:
>>
>> Sub Macro2()
>> Dim sh As Worksheet
>>
>> Set sh = ActiveWorkbook.Sheets("Sheet3")
>>
>> Application.ScreenUpdating = False
>> sh.Columns("A:B").Sort Key1:=sh.Range("B1"), _
>> Order1:=xlAscending ', _
>> Header:=xlGuess, OrderCustom:=1, _
>> MatchCase:=False, _
>> Orientation:=xlTopToBottom, _
>> DataOption1:=xlSortNormal
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Jim Thomlinson" <[email protected]> wrote in message
>> news:[email protected]...
>> > Try avoiding the select(s) something like this.
>> >
>> > Sub Macro2()
>> >
>> > Application.ScreenUpdating = False
>> > Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"),
>> > Header:=xlYes,
>> > Application.ScreenUpdating = True
>> >
>> > End Sub
>> >
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Biff" wrote:
>> >
>> >> Hi folks!
>> >>
>> >> I have this simple macro created using the recorder:
>> >>
>> >> Sub Macro2()
>> >>
>> >> Application.ScreenUpdating = False
>> >> Sheets("Sheet3").Select
>> >> Columns("A:B").Select
>> >> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
>> >> Header:=xlGuess,
>> >> _
>> >> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
>> >> _
>> >> DataOption1:=xlSortNormal
>> >> Sheets("Sheet2").Select
>> >> Application.ScreenUpdating = True
>> >>
>> >> End Sub
>> >>
>> >> All it does is sort Sheet3 A:B
>> >>
>> >> I have the macro assigned to a button on another sheet. It works fine
>> >> except
>> >> that after it runs and I go to sheet3 the sorted range, columns A:B,
>> >> are
>> >> still selected.
>> >>
>> >> How can I get the sorted range to be unselected after the macro runs?
>> >>
>> >> Thanks!
>> >>
>> >> Biff
>> >>
>> >>
>> >>
>>
>>
>>

Biff · 09-30-2005, 05:05 PM

Ok, this updated version works. I also took Jim's advice and changed Header
to xlNo since there is none!

Thanks guys!

Biff

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Jim,
>
>> Sub Macro2()
>>
>> Application.ScreenUpdating = False
>> Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
>> Application.ScreenUpdating = True
>>
>> End Sub
>
> I think that there is a subtle problem with this in that, as written, the
> sort key refers to the activesheet.
>
> I think you meant something like:
>
> Sub Macro2()
> Dim sh As Worksheet
>
> Set sh = ActiveWorkbook.Sheets("Sheet3")
>
> Application.ScreenUpdating = False
> sh.Columns("A:B").Sort Key1:=sh.Range("B1"), _
> Order1:=xlAscending ', _
> Header:=xlGuess, OrderCustom:=1, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Application.ScreenUpdating = True
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
>> Try avoiding the select(s) something like this.
>>
>> Sub Macro2()
>>
>> Application.ScreenUpdating = False
>> Sheets("Sheet3").Columns("A:B").Sort Key1:=Range("B1"), Header:=xlYes,
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "Biff" wrote:
>>
>>> Hi folks!
>>>
>>> I have this simple macro created using the recorder:
>>>
>>> Sub Macro2()
>>>
>>> Application.ScreenUpdating = False
>>> Sheets("Sheet3").Select
>>> Columns("A:B").Select
>>> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
>>> Header:=xlGuess,
>>> _
>>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>>> DataOption1:=xlSortNormal
>>> Sheets("Sheet2").Select
>>> Application.ScreenUpdating = True
>>>
>>> End Sub
>>>
>>> All it does is sort Sheet3 A:B
>>>
>>> I have the macro assigned to a button on another sheet. It works fine
>>> except
>>> that after it runs and I go to sheet3 the sorted range, columns A:B, are
>>> still selected.
>>>
>>> How can I get the sorted range to be unselected after the macro runs?
>>>
>>> Thanks!
>>>
>>> Biff
>>>
>>>
>>>
>
>