ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Unselect range (https://www.excelbanter.com/excel-programming/341587-unselect-range.html)

Biff
Unselect range
 
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[_4_]
Unselect range
 
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
Unselect range
 
Hi!

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

Sort reference is not valid.

????????

Biff

"Jim Thomlinson" wrote in message
...
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
Unselect range
 
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" wrote in message
...
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[_4_]
Unselect range
 
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" wrote in message
...
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[_4_]
Unselect range
 
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" wrote in message
...
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
Unselect range
 
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" wrote in message
...
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
Unselect range
 
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" wrote in message
...
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" wrote in message
...
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
Unselect range
 
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" wrote in message
...
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" wrote in message
...
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


All times are GMT +1. The time now is 11:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com