Selection range

[PcMax]

Hi

Here is the code OK, are there any critical issues?


Range(Cells(4, 5), Cells(Rows.Count, 5).End(3)).Select

What do you recommend?

[Admin]

Works only on ActiveSheet

may be..

With Sheet1
MsgBox .Range(.Cells(4, 5), .Cells(.Rows.Count, 5).End(3)).Address
End With

[PcMax]

Hi

Thanks to the suggestion, I thought the undocumented format I used: .Cells(.Rows.Count, 5).End(3)).Address
could create areas selection problems

[DocAElstein]

...I thought the undocumented format
..could create areas selection problems
This I can perhaps explain.

All documentation on Cells and Range____ selecting is bad. Microsoft documentation do make many mistakes. They do not even understand their own software sometimes!!
I do try here:
http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells

Ways to refer to a range ( for to Select for example )
This:
Cells(row, column) does not exist. It works by coincidence only, due to Excel VBA defaults – VBA guesses ("thinks")### here that you meant to write something else.
Cells(row, column) is not a proper way to refer to cells.

We have three ways to refer to cells, all use range things

_' (i) Application.Range("=StrRefToRangeObject") ( Method ? ) ' One or more Areas

_' (ii) Rng.Item(Item Number or co ordinate) Property ' One Cell

_' (i i i) Range(RngCorner, RngOtherCorner) Property ' One Area


Way to get a range object of all cells in an object ( for example from a Worksheet )
We cannot refer to cells using Cells( )
Cells( ) does not exist.
Cells does exist.
Cells is a Property which returns a range object, Rng
Rng = Ws.Cells

Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("Sheet") ' Set Ws = Workbooks("PcMaxRangeProperties.xls").Worksheets("Sheet1") ' Set Ws = Workbooks("PcMaxRangeProperties.xls").Worksheets.Item("Sheet1") ' Set Ws = Workbooks("PcMaxRangeProperties.xls").Worksheets.Item(1)
Dim Rng As Range: Set Rng = Ws.Cells ' Cells returns range object of all cells of object to which it is applied. here it returns all the cells of a worksheet as a single Areas range object of contiguous cells

_.______


This:_..
Range(Cells(4, 5), Cells(Rows.Count, 5).End(3)).Select
_.. is not using Cells(r, c). This is not documented because it does not exist. Cells(r, c) is not a Property

You are using
(ii) twice
and
(i i i) once

You are doing this:
(ii)a)
Rng=Ws.Cells
RngCorner = Rng.Item(4, 5)

(ii)b)
Rng=Ws.Cells
RngItem= Rng.Item(Rows.Count, 5)
RngOtherCorner = RngItem.End(3)

(i i i)
Range(RngCorner, RngOtherCorner)
This can only return one Area: So no areas selection problems

_.__________________-

VBA "thinks" for you ###
If you write
Cells
VBA "thinks" Ws.Cells or ActiveSheet.Cells

If you write
(r, c)
VBA "thinks" Item(r, c)

The Item Property is the default Property for a range object
If you write
Rng(r, c)
VBA "thinks" Rng.Item(r, c)

If you write Cells(r, c)
VBA "thinks"
First:
Rng = Ws.Cells ' Code in Worksheet Code Module
or
Rng = ActiveSheet.Cells ' Code in Normal Code Module or Code in ThisWorkbook Code Module
Second:
Rng(r, c)
Rng.Item(r, c)

( If you write Range
VBA "thinks"
Application.Range ' Code in Normal Code Module or Code in ThisWorkbook Code Module
or
Ws.Range' Code in Normal Code Module or Code in ThisWorkbook Code Module
Note: Application.Range is usually, but not always, ActiveSheet.Range http://excelmatters.com/referring-to-ranges-in-vba/#comment-197138 )

_.___________

For more than one Area, use
(i) Application.Range("=StrRefToRangeObject") ( Method ?
For example: _ Range("E1:E4,C2,G2:G3").Select
This: _ E1:E4,C2,G2:G3 _ is only approximately correct, - VBA will try to "geuss" what you want
This: _ Range _ is only approximately correct, - VBA will try to "geuss" what you want
Row\Col
B
C
D
E
F
G
H

1


2


3


4


5
Worksheet: Tabelle1
This: _ E1:E4,C2,G2:G3 _ is only approximately correct, - VBA will try to "geuss" what you want
This: _ Range _ is only approximately correct, - VBA will try to "geuss" what you want
If you write
Range("E1:E4,C2,G2:G3").Select
VBA "thinks and then guesses" :
Application.Range("=StrRefToRangeObject") _ or _ Ws.Range("=StrRefToRangeObject") _ or _ Ws.Range("Address In Ws")
( StrRefToRangeObject = Full string link to range object
Like:
StrRefToRangeObject = "='C:\Users\Elston\Desktop\[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3" )

Rem For multiple areas use _(i) Application.Range("=StrRefToRangeObject") ' http://excelmatters.com/referring-to-ranges-in-vba/
Application.Range("='C:\Users\Elston\Desktop\[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("='" & ThisWorkbook.Path & Application.PathSeparator & "[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("='[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("='Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("=$E$1:$E$4,C2,G2:G3").Select
Application.Range("$E$1:$E$4,C2,G2:G3").Select
Range("E1:E4,C2,G2:G3").Select
End Sub




Ciao
Alan



Ref:
http://excelmatters.com/referring-to-ranges-in-vba/
http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells
http://www.eileenslounge.com/viewtopic.php?f=30&t=28616#p222840





Option Explicit
Sub PcMax() ' Range(Cells(4, 5), Cells(Rows.Count, 5).End(3)).Select ' http://www.excelfox.com/forum/showthread.php/2207-Selection-range
Rem
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets.Item(1)
Rem ' Cells Property only returns a range onject of all cells of the object to which it is applied
Dim Rng As Range
Set Rng = Ws.Cells
'Rng = Cells '_- This works also

' (ii)
Rem _(ii)a) A corner cell of a single Area
Dim RngCorner As Range
Set RngCorner = Rng.Item(4, 5)
'Set RngCorner = Rng(4, 5) '_- This works also
Rem _(ii)b) The other corner cell of our single Area
Dim RngItem As Range, RngOtherCorner As Range
Set RngItem = Rng.Item(Rows.Count, 5)
Set RngOtherCorner = RngItem.End(3)

' (i i i)
Rem For a single Area we can use _(iii) Property Range(RngCorner, RngOtherCorner)
Application.Range(RngCorner, RngOtherCorner).Select

' (i)
Rem For multiple areas use _(i) Application.Range("=StrRefToRangeObject") ' http://excelmatters.com/referring-to-ranges-in-vba/
Application.Range("='C:\Users\Elston\Desktop\[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("='" & ThisWorkbook.Path & Application.PathSeparator & "[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("='[PcMaxRangeProperties.xls]Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("='Tabelle1'!$E$1:$E$4,C2,G2:G3").Select
Application.Range("=$E$1:$E$4,C2,G2:G3").Select
Application.Range("$E$1:$E$4,C2,G2:G3").Select
Range("E1:E4,C2,G2:G3").Select
End Sub