end of range question

[austin350s10]

Why is it when running the following code it returns "B35"? It should return "B32" My only thought it that since the test range only contains one cell with data it jumps down to the next range.

I have used the same code on other ranges that have more than one cell with data and it works fine.

Does anyone know of a work around?

Here is the code:

Dim newrange As String

newrange = Range("B32").End(xlDown).Address

MsgBox newrange

Here is the sheet in reference:

 

[austin350s10]

any ideas?

 

[Norie]

Why should it return B32?

 

[shg]

The code does the same thing as pressing Ctrl+Down.

 

[austin350s10]

well I am looking for the last cell that contains data before a blank cell. Lets say it started at B39 to would return B45. I am mostly using the code for larger sets of data like B39 to B45. B32 was the only set of data that was giving me a problem. Do I need to use a different code?

 

[Rick Rothstein]

newrange = Range("B32").End(xlDown).Address
 
MsgBox newrange

I guess you could do this instead...

newrange = IIf(Len(Range("B33").Value), Range("B32").End(xlDown).Address, Range("B32").Address)

 

[austin350s10]

love it....THANKS!!!