Results 1 to 4 of 4

Thread: Selection range

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13

    Selection range

    Hi

    Here is the code OK, are there any critical issues?
    Code:
    Range(Cells(4, 5), Cells(Rows.Count, 5).End(3)).Select
    What do you recommend?

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Works only on ActiveSheet

    may be..
    Code:
        With Sheet1
            MsgBox .Range(.Cells(4, 5), .Cells(.Rows.Count, 5).End(3)).Address
        End With
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13
    Hi

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

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Refering to range objects for to Select - PcMaxRangeProperties

    Quote Originally Posted by PcMax View Post
    ...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/showth...eadsheet-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

    Code:
    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...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"
    )
    Code:
    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/showth...eadsheet-cells
    http://www.eileenslounge.com/viewtop...=28616#p222840



    Code:
    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)
    
    ' (iii) 
    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
    Attached Files Attached Files
    Last edited by DocAElstein; 04-10-2018 at 11:24 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Extend the macro to a selection of cells...
    By MrBlackd in forum Excel Help
    Replies: 4
    Last Post: 09-10-2013, 01:39 PM
  2. Replies: 4
    Last Post: 11-20-2012, 05:11 PM
  3. Replies: 3
    Last Post: 04-08-2012, 08:05 AM
  4. Using Selection.Address
    By Rasm in forum Excel Help
    Replies: 1
    Last Post: 11-28-2011, 05:20 AM
  5. Mail Range or Selection using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 1
    Last Post: 11-03-2011, 09:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •