find bottom of range with value

[SamS]

I have a table which has formulas throughout. In the past to find the end of a table I used
Range("A2").End(xlDown).Select or
Range("A65536").End(xlUp).Select depending on the occasion. The problem I am having with these at the moment is that this code finds the last cell with a formula where as I specifically want the last row that has returned a value. Any ideas?

 

[Damon Ostrander]

Hi SamS,

There is no equivalent method to the End method for finding cells that contain a null value (and incidentally, a cell with a formula is not considered 'Empty' by the IsEmpty function). As a result, some extra code is required. To do the equivalent of

[a65536].End(xlUp).Select

use this code:

Dim C As Range
Set C = [a65536].End(xlUp)
Do While C = "" And C.Row > 1
Set C = C.Offset(-1)
Loop
C.Select

 

[SamS]

Thanks Damon, I was hoping that there may have been something I was missing, seems not.