Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Unselect range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,688
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,688
Default 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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default 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






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default 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






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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




Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,688
Default 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







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
unselect cells tgi Excel Discussion (Misc queries) 1 October 1st 09 03:21 AM
unselect cells tgi Excel Discussion (Misc queries) 2 October 1st 09 01:39 AM
Unselect Option Box Jon Excel Discussion (Misc queries) 1 May 18th 05 01:02 AM
Unselect a Cell?????? Andoni[_25_] Excel Programming 3 September 3rd 04 07:49 PM
Unselect Filtre .... sal21[_12_] Excel Programming 1 February 23rd 04 01:54 PM


All times are GMT +1. The time now is 12:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement