Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range data type, Selecting a column (2000)

    Hello all,

    I need to run a loop on only the populated cells in a column of my sheet. I can obtain the last occupied row using this code:

    <pre> Set oLastRow = Range("A65536").End(xlUp)

    lLastRow = Range("A65536").End(xlUp).Row
    lLastRow = lLastRow + 1
    </pre>


    I can select the first column in this last row using:

    <pre> Range("A" & lLastRow).Select
    </pre>


    and I could store this in some variable called MyLastRow and another variable I can use to store the first row.

    <pre> MyLastRow = "A" & lLastRow
    MyFirstRow = "A1"
    </pre>


    So I have enough information to select the first column. I tried

    <pre>Range(MyFirstRow:MyFirstRow)
    </pre>


    But it doesn't work. How can I use my variables (Strings) in a Range(X:Y) statement?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range data type, Selecting a column (2000)

    I think the syntax uses a comma, not a colon:

    range(startcell, endcell)

    and you can drop the locations directly into the range object like this:

    Cells(Application.Rows.Count, Range("RangeName").Column).End(xlUp).Select
    Range(ActiveCell, ActiveCell.Offset(-ActiveCell.Row + 1)).Name = "RangeName"

    ( I wrote this a few days ago so it would include any blank cells between end and start, which looks to be similar to your objective.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range data type, Selecting a column (2000)

    just a quickie but try something like this:

    <pre>Sub test()
    Dim MyFirstRow, MyLastRow, lLastRow
    Set lLastRow = Range("A65536").End(xlUp)
    lLastRow = Range("A65536").End(xlUp).Row
    lLastRow = lLastRow + 1


    MyFirstRow = "A1"
    MyLastRow = "A" & lLastRow


    Range("A" & lLastRow).Select
    Range(MyFirstRow, MyLastRow).Select
    End Sub
    </pre>


  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range data type, Selecting a column (2000)

    OK, I'm wrong, the syntax also takes a single argument,

    range("a1:b1")

    and Andrew has pointed out the problem, but in any case I hope my response was of use!
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Range data type, Selecting a column (2000)

    Kevin,

    Try <pre> Range(MyFirstRow & ":" & "A" & lLastRow)</pre>

    Edit: To include & "A"

    Andrew C

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range data type, Selecting a column (2000)

    Thanks guys!

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range data type, Selecting a column (2000)

    <pre>ActiveSheet.UsedRange.Columns(1)</pre>

    is easier.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range data type, Selecting a column (2000)

    That is handy, but if RangeName top and bottom cells are A1 to A143, and Activesheet.UsedRange is A1 to G450, the returned range from
    ActiveSheet.UsedRange.Columns(Range("RangeName").C olumn).Select
    is A1 to A450. I messed around a while and couldn't find a construct that would solve this problem; any ideas?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range data type, Selecting a column (2000)

    John, I just wanted to point out that if you have a range, then you can use Columns to pick out individual "columns" that only include cells from the original range. For example, if I know that I have a bunch on non-empty cells around A1, then to iterate through the ones in column A, I use<pre> For Each c In Range("A1").CurrentRegion.Columns(1)</pre>

    You still need to check for empty cells, but it gets the iteration down to manageable proportions. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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