Another 'Last cell in column' question

[Compass Rose]

In Excel 2003

I've searched through the archives, but couldn't find a solution to my
particular problem.

In column B, I have filled from row 2 to row 1250 with:

=IF(blah blah,"",blah blah)

To find the last row in the column that isn't blank, I tried:

lr = Cells(Rows.Count, "B").End(xlUp).Row

which returns the value of 1250.

What I need is the last row in column B where the IF condition is FALSE, and
there is text in that cell.

TIA
David

 

[Geoff K]

try something like this and substituting your real column and False result

Option Explicit

Sub testit()
Dim foundit As Range
Set foundit = Columns("E").Find(<<False result in quotes>>, , xlValues,
xlWhole, xlByRows, xlPrevious)
MsgBox foundit.Row
End Sub

hth

Geoff K

 

[Compass Rose]

It won't work because the FALSE result varies - it is a cell reference to
another sheet...

David

 

[Geoff K]

A more complete solution should also allow for a fail to find so this is
better:
Option Explicit

Sub testit()
Dim foundit As Range
Set foundit = Columns("B").Find(<<False result in quotes>>, , xlValues,
xlWhole, xlByRows, xlPrevious)
If Not foundit Is Nothing Then
MsgBox foundit.Row
Else
MsgBox "All results = True"
End If
End Sub

 

[Geoff K]

Is the True result a constant then?

Geoff K

It won't work because the FALSE result varies - it is a cell reference to
another sheet...

David

 

[Geoff K]

If the False result is a ref to another sheeet there must be some sheet id
which can be used to differentiate from True even if it is only the word
"Sheet" ? If that is the case then change the find statement from xlWhole to
xlPart and use "Sheet".

hth

Geoff

 

[Compass Rose]

The formula in B2 is:

=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!X:X,SMALL(A:A,ROWS($1:1))+9))

The formula in B1250 is:

=IF(ROWS($1:1249)>COUNT(A:A),"",INDEX(Sheet1!X:X,SMALL(A:A,ROWS
_($1:1249))+9))

Your statement Set foundit = Columns("B").Find("Sheet1", , xlValues,
Currently, cells B2 to B451 have data in them because the IF is FALSE. Cells
B452 to B1249 are "" (blank) because the IF is TRUE. I want to sort the range
B2:F451 (lastrow), using column B as the key. I need to find the last row
that has data in it so that I'm not sorting B2:F1250. In a week from now, the
data in column B may go to row 520, so I'll want to sort B2:F520.

I hope this clarifies my problem.

David

 

[Geoff K]

If this results in 1250 then 1250 cannot be blank
Set foundit = Columns("B").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)

If this does not work then I apologise if I have created false hope.

Geoff K