Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    define a range based on two single cells (Excel97/

    How do I define a range based on two single cells?

    For the particular exercise, I have discovered that if I am passed a two-dimensional array (varAr), I can load that data to the activesheet if I have a range object (rngNew) defined with the same number of rows & columns as the incoming array.
    <pre> rngNew=varAr
    </pre>

    is concise and effective.


    I can't work out how to define that range object, targeted as rngNew in the code below.


    If I know the named range that includes the top left-hand corner of my target, then I can refer to the first cell in that range - rng.Cells(1, 1) - and hence rngTop is a range object that describes one cell, specifically the cell at the top left hand corner

    Likewise rngBot defines the one cell at the bottom RH corner - rngTop.Offset(j, i).Cells(1, 1)


    I'm stuck at the point of combining rngTop and rngBot to produce rngNew, and I need help!


    <pre>Sub TESTRange()

    ' Create basic range object based on a user-defined named range of any size.
    Dim rng As Range
    Set rng = Range("TopLhCorner") ' User must have defined the named range in advance
    rng.Select ' proof

    ' Create a simple range object to describe the top-lH corner of the original range
    Dim rngTop As Range
    Set rngTop = rng.Cells(1, 1)
    rngTop.Select ' proof

    ' Fake the dimensions of the data array to be stored (usually obtained by the UBound function)
    Dim i As Integer
    i = 3
    Dim j As Integer
    j = 8

    ' Create a simple range object to describe the bottom RH corner of the propsoed range
    Dim rngBot As Range
    Set rngBot = rngTop.Offset(j, i).Cells(1, 1)
    rngBot.Select ' proof

    ' Now define a single range object that extends from topLH to bottomRH
    Dim rngNew As Range
    set rngnew=????

    End Sub
    </pre>


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: define a range based on two single cells (Excel97/

    Chris,

    If I understand your requirement, given the firste cell ( rng.Cells(1, 1)), and the number of rows, columns, you should not need to worry about defining the bottom RH cell.

    Try :

    Set rngnew = Range(rng.Cells(1, 1), rng.Cells(1, 1).Offset(j-1, i-1))

    Andrew

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: define a range based on two single cells (Exce

    Thanks, Andrew. It worked perfectly.

    Not only that, but it removed several interim lines of code - my establishment of the TopLH and BottomRH corners. Neat is, well, "neat"!

    Although at first it didn't work at all, and I couldn't understand why ("It's from Andrew; it HAS to work .....".

    Once I placed the "rngNew.Select" after your line of code I could SEE the results. hah hah

Posting Permissions

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