Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Unanswered: Find a range in VBA

    I need to find the bounds of a range in VBA that begins at a fixed point say A1 and ends at a point to the right and at a point downward that is unknown. Once found I need to set a range object = to the range found and then process the contents of that range. What is the best way to do this? Any help appreciated thanks.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    How are you supposed to know when you reach the end of the range, either to the right or downwards? Is it the first blank cell? Is there some special value/formula in it?

    Sam

  3. #3
    Join Date
    Apr 2004
    Posts
    173
    Sorry, I haven't checked my box in a while. Typically I would be looking for an empty cell. I've tried using .end(xlright) or .end(xldown), but for some odd reason it doesn't always return the expected result. So, I've ended up looping to find the cell and that seems a little convoluted to get something so simple.

    Thanks for your earlier reply.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Typically I would be looking for an empty cell.
    That's what I thought. If - and only if - you have no blank cells in the middle of the range, all you need to do is initialize two counters: one for the rows and one for columns. For the row counter, you might code
    Code:
    Dim RowCntr As Long, ColCntr As Long, RngEnd As String, DataRng As Range
    
    RowCntr = 1    'If you use column headings, or have blank rows on top, start with the correct row instead of 1
    Do While Range("A" & RowCntr) > " "
        RowCntr = RowCntr + 1
    Loop
    Of course at this point you've gone beyond the end of your data, so add
    Code:
    RowCntr = RowCntr - 1
    Now you're back into the data.
    Now do the column thing. This is different, though, because there's no way to increment columns directly in VBA, so you have to use the Chr() function. It's the same concept as before, only you don't start with 1.
    Code:
    ColCntr = 65
    Do While Range(Chr(ColCntr) & "1") > " "    'Chr(65) = "A"
        ColCntr = ColCntr + 1
    Loop
    ColCntr = ColCntr - 1
    Now you've identified the lower right-hand corner of the range.
    Code:
    RngEnd = Chr(ColCntr) & CStr(RowCntr)
    DataRng = "A1:" & RngEnd
    If you do have blank cells, or if your data goes beyond column 'Z', you need an entirely different algorithm, but first things first.

    Hope This Helps,

    Sam
    Last edited by Sam Landy; 07-06-07 at 10:49.

  5. #5
    Join Date
    Apr 2004
    Posts
    173
    Yes this helps, and thanks for the code. This is much appreciated.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi
    I've tried using .end(xlright) or .end(xldown), but for some odd reason it doesn't always return the expected result.
    If it is a fully filled range (at least filled Row and Column), then it would be interesting to know why this does not work?

    Also, if you allways start at "A1", then this is a little simpler ?

    Code:
    Sub SelectRange()
        Dim i As Long
        Dim j As Long
        
        j = 1
        i = 1
        Do While Cells(i, j) <> ""
            j = j + 1
            If j > 256 Then Exit Do
        Loop
        j = j - 1
        Do While Cells(i, j) <> ""
            i = i + 1
            If i > 65536 Then Exit Do
        Loop
        i = i - 1
        Range(Cells(1, 1), Cells(i, j)).Select
        
    End Sub

    ??


    MTB

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    Why not use CurrentRegion or UsedRange?

    I don't see any need for looping.
    Code:
    Set rng = Range("A1").CurrentRegion
    Msgbox rng.Address

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Well CurrentRange does it.

    I don't recall this being available in Office 97 when I was writing my own 'CurrentRange' function !?

    I guess I should rummage round a bit more to see what else is in there !!??

    Have we landed on the moon yet?

    MTB

Posting Permissions

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