Last cell in range of formulas not equal to blank

[Shawn]

I have a range: A1:E10. Every cell in this range has a formula in it that
looks in other sheets and makes calculations. If the cell value is 0 it
leaves the cell blank.

I want to find the last cell (bottom right) in this range that does not
equal "blank".

Help.

 

[Gary''s Student]

Sub find_it()
mesage = ""
Set r = Range("A1:E10")
For Each rr In r
If rr.Value = "" Then
Else
mesage = rr.Address
End If
Next
MsgBox (mesage)
End Sub

 

[Shawn]

Thank you. Can you help me one step farther? I want to go to that cell and
select the range A1: that cell???

 

[Gary''s Student]

We can do either, but I don't know how to do both at the same time. This
will select the Found cell:

Sub find_it()
mesage = ""
Set rGoTo = Range("A1")
Set r = Range("A1:E10")
For Each rr In r
If rr.Value = "" Then
Else
mesage = rr.Address
Set rGoTo = rr
End If
Next
rGoTo.Select
End Sub

This version selects the area from A1 thru the Found cell:

Sub find_it()
mesage = ""
Set rGoTo = Range("A1")
Set r = Range("A1:E10")
For Each rr In r
If rr.Value = "" Then
Else
mesage = rr.Address
Set rGoTo = rr
End If
Next
Range("A1:" & mesage).Select
End Sub