Find first non-blank cell

[Otto Moehrbach]

Excel XP, Win Vista
I have a table of formulas that produce blank cells on condition. To set
the print range I want to find the first non-blank cell in Column A looking
from the bottom up.
Doing End(xlUp) will hit on the first formula cell (looking up) and not the
first non-blank cell. Looping up through Column A looking for a value of
not zero will find what I want.
Question: Is there a better way? Thanks for your time. Otto

 

[Bob Phillips]

Dim rng As Range

Set rng = Range("C" & Rows.Count).End(xlUp)
Set rng = rng.End(xlUp)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Slight correctrion

Dim rng As Range

Set rng = Range("C" & Rows.Count).End(xlUp)
Do Until rng.Value <> ""
Set rng = rng.End(xlUp)
Loop


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Thomlinson]

Here is some code that leverages find...

Sub test2()
MsgBox LastCell(rng:=Sheet1.Range("B:B")).Address
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet, Optional rng As
Range) As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long

If wks Is Nothing Then Set wks = ActiveSheet
If rng Is Nothing Then rng = wks.Cells
On Error Resume Next
lngLastRow = rng.Find(What:="*", _
After:=rng(1), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lngLastColumn = rng.Find(What:="*", _
After:=rng(1), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
lngLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, lngLastColumn)

End Function

 

[Otto Moehrbach]

Bob, Jim
Thanks for the help. Otto