Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range/Address (excel2000VBA)

    The attatched macro works up until Debug.Print CellŪ.Address. The immediate window show the Address of CellŪ.
    My goal is to be able to use :Range(Top:Bottom) but I can't seem to be able to get a handle on it.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range/Address (excel2000VBA)

    What exactly are you trying to accomplish? I don't understand your code: first you select a single cell B1, then you endlessly loop through all cells of the selection without an escape. You never set the Range variables named Top and Bottom, but you do create named ranges Top and Bottom; they don't refer to cells however but to string constants.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range/Address (excel2000VBA)

    What I'm trying to do is create a Range---Range(Top:Bottom). That is the first cell in the column and the last selected cell. B1:B9.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range/Address (excel2000VBA)

    You can do that quite easily without using loops:

    Dim Range2 As Range
    Set Range2 = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range/Address (excel2000VBA)

    Doesn't that code count all the rows?
    In my worksheet, B1:B9 contain the same values. B10's value is different. So I just want the range with the same values.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range/Address (excel2000VBA)

    You didn't say that in "That is the first cell in the column and the last selected cell." (Remember that you only posted code, not a workbook)
    Try this instead:

    Sub Test()
    Dim rngTop As Range
    Dim rngBottom As Range
    Dim rng As Range
    Set rngTop = Range("B1")
    Set rngBottom = rngTop
    Do While rngBottom = rngTop
    Set rngBottom = rngBottom.Offset(1, 0)
    Loop
    Set rng = Range(rngTop, rngBottom.Offset(-1, 0))
    ' Do something with the range
    Debug.Print rng.Address
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range/Address (excel2000VBA)

    Both your code and mine come up with the same Address. B1:B9.
    For some reason I cannot copy that range, say, to Column D.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range/Address (excel2000VBA)

    You can use the Copy method to copy a range of cells to another range.

Posting Permissions

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