Find first non-blank cell

O

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
 
B

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)
 
B

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)
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top