Find a range in VBA

mmbosman · #1 (**permalink**) 06-18-07, 14:54

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.

Sam Landy · #2 (**permalink**) 06-27-07, 17:38

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

mmbosman · #3 (**permalink**) 07-06-07, 07:52

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.

Sam Landy · #4 (**permalink**) 07-06-07, 09:38

Quote:

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

mmbosman · #5 (**permalink**) 07-06-07, 17:45

Yes this helps, and thanks for the code. This is much appreciated.

MikeTheBike · #6 (**permalink**) 07-09-07, 07:55

Hi

Quote:

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

norie · #7 (**permalink**) 07-15-07, 15:49

Why not use CurrentRegion or UsedRange?

I don't see any need for looping.

Code:

Set rng = Range("A1").CurrentRegion
Msgbox rng.Address

MikeTheBike · #8 (**permalink**) 07-16-07, 09:02

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

Internet Services

Web Development

Digital Marketing

Consumer Tech