Is .Select ever needed?
 

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

Is .Select ever needed?
 

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

"John Coleman" wrote in message
oups.com...
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

Is .Select ever needed?
 

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" wrote in message
oups.com...
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

Is .Select ever needed?
 

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" wrote in message
oups.com...
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

Is .Select ever needed?
 

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


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=494434

Is .Select ever needed?
 

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

Mike F
"Don Guillett" wrote in message
...
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

"John Coleman" wrote in message
oups.com...
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

Is .Select ever needed?
 

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

Is .Select ever needed?
 

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" skrev i melding
oups.com...
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

Is .Select ever needed?
 

Hi Chip

I think you can avoid that one too:

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

Best wishes Harald

"Chip Pearson" skrev i melding
...
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" wrote in message
oups.com...
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

Is .Select ever needed?
 

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" skrev i melding
...
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" wrote in message
oups.com...
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

Is .Select ever needed?
 

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" wrote in message
oups.com...
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

Is .Select ever needed?
 

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

Is .Select ever needed?
 

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

Is .Select ever needed?
 

On Sun, 18 Dec 2005 09:27:01 -0600, Chip Pearson 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).

Is .Select ever needed?
 

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

Is .Select ever needed?
 

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" wrote in message
...
On Sun, 18 Dec 2005 09:27:01 -0600, Chip Pearson
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).

Is .Select ever needed?
 

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

Is .Select ever needed?
 

or use application.goto

--
Don Guillett
SalesAid Software

"Chip Pearson" wrote in message
...
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" wrote in message
...
On Sun, 18 Dec 2005 09:27:01 -0600, Chip Pearson
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).

Is .Select ever needed?
 

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

Dm Unseen

Is .Select ever needed?
 

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

--
Gordon Rainsford

London UK

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

Is .Select ever needed?
 

Hi Gordon,

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


---
Regards,
Norman



"Gordon Rainsford" wrote in message
...
Can someone tell me how to copy and paste a range that has been
autofiltered, without selecting it?

--
Gordon Rainsford

London UK

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

Is .Select ever needed?
 

(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 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

Is .Select ever needed?
 

Thanks, Norman

--
Gordon Rainsford

London UK


Norman Jones wrote:

Hi Gordon,

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


---
Regards,
Norman



"Gordon Rainsford" wrote in message
...
Can someone tell me how to copy and paste a range that has been
autofiltered, without selecting it?

--
Gordon Rainsford

London UK

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