Is .Select ever needed?

[John Coleman]

Greetings,
I tend to regard .Select as the goto of Excel VBA: it is seldom
necessary and makes code harder to read, not to mention slower.
Nevertheless, I do sometimes use it - but (if I am honest) only in
cases where I do not really understand the object model (which in my
case includes things like PasteSpecial and Sort) and thus rely on
editing code from the macro recorder. Is it *ever* necessary to select
a range before performing some operation involving it? Just curious.

Have a good day

-John Coleman

[Don Guillett]

seldom
range("a1:x12").select
selection.sort
can be
range("a1:x12").sort

pastevalues can be
range("a1:x12").value = range("b1:y12").value
range(destingationrange).value = range(sourcerange).value

--
Don Guillett
SalesAid Software
[email protected]
"John Coleman" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only in
> cases where I do not really understand the object model (which in my
> case includes things like PasteSpecial and Sort) and thus rely on
> editing code from the macro recorder. Is it *ever* necessary to select
> a range before performing some operation involving it? Just curious.
>
> Have a good day
>
> -John Coleman
>

[RB Smissaert]

Don't have to select to sort:

Range(Cells(1), Cells(5, 3)).Sort Key1:=Cells(1), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom

RBS

"John Coleman" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only in
> cases where I do not really understand the object model (which in my
> case includes things like PasteSpecial and Sort) and thus rely on
> editing code from the macro recorder. Is it *ever* necessary to select
> a range before performing some operation involving it? Just curious.
>
> Have a good day
>
> -John Coleman
>

[Chip Pearson]

You need to Select the appropriate cell when calling FreezePanes.
Off the top of my head, that's the only time you need to use
Select.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"John Coleman" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is
> seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only
> in
> cases where I do not really understand the object model (which
> in my
> case includes things like PasteSpecial and Sort) and thus rely
> on
> editing code from the macro recorder. Is it *ever* necessary to
> select
> a range before performing some operation involving it? Just
> curious.
>
> Have a good day
>
> -John Coleman
>

[Myles]

John,

The simple answer is GENERALLY NO. It is superfluous to select prior to perfoming an operation on an object - cell, range, worksheet etc.

But still on the subject of selecting, I have encountered situations where activating a Worksheet using SELECT failed. In all such cases, the looping context needed the ACTIVATE property. Curious encounters!

Myles

[Mike Fogleman]

Also PasteAll can be
Range("B1:Y12").Copy Destination: = Range("A1:X12")

Mike F
"Don Guillett" <[email protected]> wrote in message
news:ey9mka%[email protected]...
> seldom
> range("a1:x12").select
> selection.sort
> can be
> range("a1:x12").sort
>
> pastevalues can be
> range("a1:x12").value = range("b1:y12").value
> range(destingationrange).value = range(sourcerange).value
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "John Coleman" <[email protected]> wrote in message
> news:[email protected]...
>> Greetings,
>> I tend to regard .Select as the goto of Excel VBA: it is seldom
>> necessary and makes code harder to read, not to mention slower.
>> Nevertheless, I do sometimes use it - but (if I am honest) only in
>> cases where I do not really understand the object model (which in my
>> case includes things like PasteSpecial and Sort) and thus rely on
>> editing code from the macro recorder. Is it *ever* necessary to select
>> a range before performing some operation involving it? Just curious.
>>
>> Have a good day
>>
>> -John Coleman
>>
>
>

[Dave Peterson]

I think changing the zoom factor needs selection, too. Well, when you want to
fit to a certain range.





John Coleman wrote:
>
> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only in
> cases where I do not really understand the object model (which in my
> case includes things like PasteSpecial and Sort) and thus rely on
> editing code from the macro recorder. Is it *ever* necessary to select
> a range before performing some operation involving it? Just curious.
>
> Have a good day
>
> -John Coleman

--

Dave Peterson

[Harald Staff]

Hi John

There are quite a few things that ActiveChart can do that Charts(1) can't,
like accessing and manipulating its data series. That's the only thing I use
Select for, I haven't selected a range by code for years, I'd say no.

HTH. Best wishes Harald

"John Coleman" <[email protected]> skrev i melding
news:[email protected]...
> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only in
> cases where I do not really understand the object model (which in my
> case includes things like PasteSpecial and Sort) and thus rely on
> editing code from the macro recorder. Is it *ever* necessary to select
> a range before performing some operation involving it? Just curious.
>
> Have a good day
>
> -John Coleman
>

[Harald Staff]

Hi Chip

I think you can avoid that one too:

ActiveWindow.SplitRow = 3
ActiveWindow.SplitColumn = 2
ActiveWindow.FreezePanes = True

Best wishes Harald

"Chip Pearson" <[email protected]> skrev i melding
news:ePNO$d%[email protected]...
> You need to Select the appropriate cell when calling FreezePanes.
> Off the top of my head, that's the only time you need to use
> Select.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "John Coleman" <[email protected]> wrote in message
> news:[email protected]...
> > Greetings,
> > I tend to regard .Select as the goto of Excel VBA: it is
> > seldom
> > necessary and makes code harder to read, not to mention slower.
> > Nevertheless, I do sometimes use it - but (if I am honest) only
> > in
> > cases where I do not really understand the object model (which
> > in my
> > case includes things like PasteSpecial and Sort) and thus rely
> > on
> > editing code from the macro recorder. Is it *ever* necessary to
> > select
> > a range before performing some operation involving it? Just
> > curious.
> >
> > Have a good day
> >
> > -John Coleman
> >
>
>

[Dave Peterson]

But can you do it without selecting the sheet (assuming it's not showing on
another window)?

Once you, er, I select the sheet, I think the .select camel's nose is in the
tent. Why not invite the rest of him?



Harald Staff wrote:
>
> Hi Chip
>
> I think you can avoid that one too:
>
> ActiveWindow.SplitRow = 3
> ActiveWindow.SplitColumn = 2
> ActiveWindow.FreezePanes = True
>
> Best wishes Harald
>
> "Chip Pearson" <[email protected]> skrev i melding
> news:ePNO$d%[email protected]...
> > You need to Select the appropriate cell when calling FreezePanes.
> > Off the top of my head, that's the only time you need to use
> > Select.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> > "John Coleman" <[email protected]> wrote in message
> > news:[email protected]...
> > > Greetings,
> > > I tend to regard .Select as the goto of Excel VBA: it is
> > > seldom
> > > necessary and makes code harder to read, not to mention slower.
> > > Nevertheless, I do sometimes use it - but (if I am honest) only
> > > in
> > > cases where I do not really understand the object model (which
> > > in my
> > > case includes things like PasteSpecial and Sort) and thus rely
> > > on
> > > editing code from the macro recorder. Is it *ever* necessary to
> > > select
> > > a range before performing some operation involving it? Just
> > > curious.
> > >
> > > Have a good day
> > >
> > > -John Coleman
> > >
> >
> >

--

Dave Peterson

[Bob Umlas]

Frequently, after a macro runs, I like to select cell A1 to leave the user
there, especially if the macro is generating a report on a sheet which gets
created by the macro, or if the macro is taking the user to a sheet where
he's supposed to enter data I'll usually place the cursor in the cell to
contain the first legitimate date.

Bob Umlas
Excel MVP

"John Coleman" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only in
> cases where I do not really understand the object model (which in my
> case includes things like PasteSpecial and Sort) and thus rely on
> editing code from the macro recorder. Is it *ever* necessary to select
> a range before performing some operation involving it? Just curious.
>
> Have a good day
>
> -John Coleman
>

[davidm]

I have a Listbox fed by RowSource delivering data from Cols A-C. The code below
deletes a selected row from both the ListBox and the root row on the worksheet. It works fine except that, try as I would, it does the job uglily. For one, I have violated one of the cardinal principles of good programming by pandering to the use of GO TO in a way which makes the code poorly structured.

Could someone kindly have a quick study and restructure the logical flow without having to loop backwards the way I did? Many thanks.

[PS: I would also love the code to allow for multiple row selection and resultant block deletions, if possible].


David.

Private Sub CmdDelete_Click()

Restart:
If ListBox1.ListIndex = -1 Then 'no selection
ans = MsgBox("Select item to delete", vbYesNo + vbDefaultButton2)
If ans = vbYes Then
ListBox1.Selected(0) = True 'select 1st item for a start
GoTo Skip
Else
ListBox1.ListIndex = -1
Exit Sub
End If
End If

Skip:
If ListBox1.Selected(1) =False True Then
If ListBox1.Selected(ListBox1.ListIndex) = True Then
ansx = MsgBox("Do you wish to delete selection?" & vbCrLf & " " & ListBox1.List(ListBox1.ListIndex, 0), vbYesNo + vbDefaultButton2 + vbInformation)
If ansx = vbNo Then Exit Sub
ActiveSheet.Cells(ListBox1.ListIndex + 1, 1).Resize(, 3).ClearContents
On Error Resume Next
ListBox1.Selected(ListBox1.ListIndex) = False
ansx = MsgBox("Do you wish to delete another?", vbYesNo + vbDefaultButton1 + vbInformation)
If ansx = vbNo Then
GoTo Sortt
Else
GoTo Restart
End If
End If
End If


Sortt:
Columns("a:c").Sort Key1:=Range("A2"), Key2:=Range("b2"), Key3:=Range("c2"), Header:=xlNo

ListBox1.RowSource = "a1:c" & [a65536].End(xlUp).Row

End Sub

[Dave Peterson]

A listbox can support multiple selections. Maybe you could use that to get all
the rows that that should be deleted/cleared.

I put 2 buttons (cmddelete and cmdcancel) and one listbox (listbox1) on a
userform.

This was the code behind that userform:

Option Explicit
Dim BlkProc As Boolean
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdDelete_Click()

Dim iCtr As Long
Dim myRng As Range
Dim RngToClear As Range
Dim myArea As Range
Dim resp As Long

resp = MsgBox(Prompt:="Are you sure?", Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

Set RngToClear = Nothing

With Me.ListBox1
For iCtr = .ListCount - 1 To 0 Step -1
If .Selected(iCtr) Then
If RngToClear Is Nothing Then
Set RngToClear _
= Application.Range(.RowSource).Rows(iCtr + 1).Cells(1)
Else
Set RngToClear = Union(RngToClear, _
Application.Range(.RowSource).Rows(iCtr + 1).Cells(1))
End If
End If
Next iCtr
End With

If RngToClear Is Nothing Then
'do nothing
Else
For Each myArea In RngToClear.Areas
myArea.Resize(, 3).ClearContents
Next myArea
With Worksheets("Sheet1")
With .Range("a:c")
.Cells.Sort key1:=.Columns(1), order1:=xlascending, _
key2:=.Columns(2), order2:=xlascending, _
key3:=.Columns(3), order3:=xlascending, _
header:=xlNo
Set myRng _
= .Range("a1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
End With
Me.cmdDelete.Enabled = False
If Application.CountA(myRng) = 0 Then
'no more data
Me.ListBox1.RowSource = ""
Me.ListBox1.Clear
Else
Me.ListBox1.RowSource = myRng.Address(external:=True)
End If

End If

End Sub

Private Sub ListBox1_Change()
Dim iCtr As Long

If BlkProc = True Then Exit Sub

Me.cmdDelete.Enabled = False

With Me.ListBox1
For iCtr = 0 To .ListCount
If .Selected(iCtr) Then
Me.cmdDelete.Enabled = True
Exit For
End If
Next iCtr
End With

End Sub

Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

If Application.CountA(myRng) = 0 Then
'do nothing
Else
With Me.ListBox1
BlkProc = True
.MultiSelect = fmMultiSelectMulti
.ColumnCount = 3
.RowSource = myRng.Address(external:=True)
BlkProc = False
End With
End If

Me.cmdDelete.Enabled = False
Me.cmdCancel.Caption = "Cancel"
Me.cmdDelete.Caption = "Delete"

End Sub

davidm wrote:
>
> I have a Listbox fed by RowSource delivering data from Cols A-C. The
> code below
> deletes a selected row from both the ListBox and the root row on the
> worksheet. It works fine except that, try as I would, it does the job
> uglily. For one, I have violated one of the cardinal principles of -good
> programming- by pandering to the use of GO TO in a way which makes the
> code poorly structured.
>
> Could someone kindly have a quick study and restructure the logical
> flow without having to loop backwards the way I did? Many thanks.
>
> [PS: I would also love the code to allow for multiple row selection and
> resultant block deletions, if possible].
>
> David.
>
> Private Sub CmdDelete_Click()
>
> Restart:
> If ListBox1.ListIndex = -1 Then 'no selection
> ans = MsgBox("Select item to delete", vbYesNo + vbDefaultButton2)
> If ans = vbYes Then
> ListBox1.Selected(0) = True 'select 1st item for a start
> GoTo Skip
> Else
> ListBox1.ListIndex = -1
> Exit Sub
> End If
> End If
>
> Skip:
> If ListBox1.Selected(1) =False True Then
> If ListBox1.Selected(ListBox1.ListIndex) = True Then
> ansx = MsgBox("Do you wish to delete selection?" & vbCrLf & "
> " & ListBox1.List(ListBox1.ListIndex, 0), vbYesNo +
> vbDefaultButton2 + vbInformation)
> If ansx = vbNo Then Exit Sub
> ActiveSheet.Cells(ListBox1.ListIndex + 1, 1).Resize(,
> 3).ClearContents
> On Error Resume Next
> ListBox1.Selected(ListBox1.ListIndex) = False
> ansx = MsgBox("Do you wish to delete another?", vbYesNo +
> vbDefaultButton1 + vbInformation)
> If ansx = vbNo Then
> GoTo Sortt
> Else
> GoTo Restart
> End If
> End If
> End If
>
> Sortt:
> Columns("a:c").Sort Key1:=Range("A2"), Key2:=Range("b2"),
> Key3:=Range("c2"), Header:=xlNo
>
> ListBox1.RowSource = "a1:c" & [a65536].End(xlUp).Row
>
> End Sub
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=494434

--

Dave Peterson

[Don Wiss]

On Sun, 18 Dec 2005 09:27:01 -0600, Chip Pearson <[email protected]> wrote:

>You need to Select the appropriate cell when calling FreezePanes.
>Off the top of my head, that's the only time you need to use
>Select.

What if one wants to position the cursor in a certain cell on a sheet that
is not the active sheet?

Don <www.donwiss.com> (e-mail link at home page bottom).

[DM Unseen]

Another issue is with editing/creating Validation or Conditional
formatting through VBA. When using XL formula(s) in the condition(s)
that have relative references selecting/activating the correct
sheet/range is mandatory, else your formulas will not work.

DM Unseen

[Chip Pearson]

In that case you have to Select or Activate the sheet first, then
select the cell.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Don Wiss" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On Sun, 18 Dec 2005 09:27:01 -0600, Chip Pearson
> <[email protected]> wrote:
>
>>You need to Select the appropriate cell when calling
>>FreezePanes.
>>Off the top of my head, that's the only time you need to use
>>Select.
>
> What if one wants to position the cursor in a certain cell on a
> sheet that
> is not the active sheet?
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

[Dave Peterson]

John Walkenbach gives a workaround at:
http://j-walk.com/ss/excel/odd/odd07.htm

DM Unseen wrote:
>
> Another issue is with editing/creating Validation or Conditional
> formatting through VBA. When using XL formula(s) in the condition(s)
> that have relative references selecting/activating the correct
> sheet/range is mandatory, else your formulas will not work.
>
> DM Unseen

--

Dave Peterson

[Don Guillett]

or use application.goto

--
Don Guillett
SalesAid Software
[email protected]
"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> In that case you have to Select or Activate the sheet first, then select
> the cell.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Don Wiss" <donwiss@no_spam.com> wrote in message
> news:[email protected]...
>> On Sun, 18 Dec 2005 09:27:01 -0600, Chip Pearson <[email protected]>
>> wrote:
>>
>>>You need to Select the appropriate cell when calling FreezePanes.
>>>Off the top of my head, that's the only time you need to use
>>>Select.
>>
>> What if one wants to position the cursor in a certain cell on a sheet
>> that
>> is not the active sheet?
>>
>> Don <www.donwiss.com> (e-mail link at home page bottom).
>
>

[DM Unseen]

I had probably seen it in the past, but I totally forgot, thanks
pointing it out, I'll use it right away

Dm Unseen

[Gordon Rainsford]

Can someone tell me how to copy and paste a range that has been
autofiltered, without selecting it?

--
Gordon Rainsford

London UK

John Coleman <[email protected]> wrote:

> Greetings,
> I tend to regard .Select as the goto of Excel VBA: it is seldom
> necessary and makes code harder to read, not to mention slower.
> Nevertheless, I do sometimes use it - but (if I am honest) only in
> cases where I do not really understand the object model (which in my
> case includes things like PasteSpecial and Sort) and thus rely on
> editing code from the macro recorder. Is it *ever* necessary to select
> a range before performing some operation involving it? Just curious.
>
> Have a good day
>
> -John Coleman

[Norman Jones]

Hi Gordon,

ActiveSheet.AutoFilter.Range.Copy _
Destination:=Sheets("Sheet5").Range("A1")


---
Regards,
Norman



"Gordon Rainsford" <[email protected]> wrote in message
news:1h7w9k7.bw5s19wvh4oiN%[email protected]...
> Can someone tell me how to copy and paste a range that has been
> autofiltered, without selecting it?
>
> --
> Gordon Rainsford
>
> London UK
>
> John Coleman <[email protected]> wrote:
>
>> Greetings,
>> I tend to regard .Select as the goto of Excel VBA: it is seldom
>> necessary and makes code harder to read, not to mention slower.
>> Nevertheless, I do sometimes use it - but (if I am honest) only in
>> cases where I do not really understand the object model (which in my
>> case includes things like PasteSpecial and Sort) and thus rely on
>> editing code from the macro recorder. Is it *ever* necessary to select
>> a range before performing some operation involving it? Just curious.
>>
>> Have a good day
>>
>> -John Coleman
>
>

[Dave Peterson]

(assumes that you've applied the filter arrows already).

Option Explicit
Sub testme()
Dim RngToCopy As Range

With Worksheets("Sheet1")
With .AutoFilter.Range
Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = .Resize(.Rows.Count - 1, .Columns.Count) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
End With

If RngToCopy Is Nothing Then
'no visible rows--except the header
Else
RngToCopy.Copy _
Destination:=Worksheets("sheet2").Range("a1")
End If

End Sub

It copies the details--not the header.

Gordon Rainsford wrote:
>
> Can someone tell me how to copy and paste a range that has been
> autofiltered, without selecting it?
>
> --
> Gordon Rainsford
>
> London UK
>
> John Coleman <[email protected]> wrote:
>
> > Greetings,
> > I tend to regard .Select as the goto of Excel VBA: it is seldom
> > necessary and makes code harder to read, not to mention slower.
> > Nevertheless, I do sometimes use it - but (if I am honest) only in
> > cases where I do not really understand the object model (which in my
> > case includes things like PasteSpecial and Sort) and thus rely on
> > editing code from the macro recorder. Is it *ever* necessary to select
> > a range before performing some operation involving it? Just curious.
> >
> > Have a good day
> >
> > -John Coleman

--

Dave Peterson

[Gordon Rainsford]

Thanks, Norman

--
Gordon Rainsford

London UK


Norman Jones <[email protected]> wrote:

> Hi Gordon,
>
> ActiveSheet.AutoFilter.Range.Copy _
> Destination:=Sheets("Sheet5").Range("A1")
>
>
> ---
> Regards,
> Norman
>
>
>
> "Gordon Rainsford" <[email protected]> wrote in message
> news:1h7w9k7.bw5s19wvh4oiN%[email protected]...
> > Can someone tell me how to copy and paste a range that has been
> > autofiltered, without selecting it?
> >
> > --
> > Gordon Rainsford
> >
> > London UK
> >
> > John Coleman <[email protected]> wrote:
> >
> >> Greetings,
> >> I tend to regard .Select as the goto of Excel VBA: it is seldom
> >> necessary and makes code harder to read, not to mention slower.
> >> Nevertheless, I do sometimes use it - but (if I am honest) only in
> >> cases where I do not really understand the object model (which in my
> >> case includes things like PasteSpecial and Sort) and thus rely on
> >> editing code from the macro recorder. Is it *ever* necessary to select
> >> a range before performing some operation involving it? Just curious.
> >>
> >> Have a good day
> >>
> >> -John Coleman
> >
> >