Another 'Last cell in column' question

  • Thread starter Thread starter Compass Rose
  • Start date Start date
C

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
 
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
 
It won't work because the FALSE result varies - it is a cell reference to
another sheet...

David
 
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
 
Is the True result a constant then?

Geoff K

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

David
 
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
 
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
 
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
 

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

Back
Top