Last cell in range of formulas not equal to blank

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shawn - 26 Mar 2008 13:03 GMT

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.

Signature

Thanks
Shawn

Reply to this Message

Gary''s Student - 26 Mar 2008 13:21 GMT

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

Signature

Gary''s Student - gsnu200775

> 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
[quoted text clipped - 4 lines]
>
> Help.

Reply to this Message

Shawn - 26 Mar 2008 13:26 GMT

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

Signature

Thanks
Shawn

> Sub find_it()
> mesage = ""
[quoted text clipped - 16 lines]
> >
> > Help.

Reply to this Message

Gary''s Student - 26 Mar 2008 13:38 GMT

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

Signature

Gary''s Student - gsnu200775

> Thank you. Can you help me one step farther? I want to go to that cell and
> select the range A1: that cell???
[quoted text clipped - 19 lines]
> > >
> > > Help.

Reply to this Message