Find first non-blank cell
 

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

Find first non-blank cell
 

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)

"Otto Moehrbach" wrote in message
...
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

Find first non-blank cell
 

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)

"Otto Moehrbach" wrote in message
...
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

Find first non-blank cell
 

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
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

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

Find first non-blank cell
 

Bob, Jim
Thanks for the help. Otto
"Otto Moehrbach" wrote in message
...
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