Another 'Last cell in column' question
 

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

Another 'Last cell in column' question
 

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" wrote:

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

Another 'Last cell in column' question
 

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

David

"Geoff K" wrote:

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" wrote:

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

Another 'Last cell in column' question
 

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" wrote:

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" wrote:

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

Another 'Last cell in column' question
 

Is the True result a constant then?

Geoff K

"Compass Rose" wrote:

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

David

"Geoff K" wrote:

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" wrote:

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

Another 'Last cell in column' question
 

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

"Geoff K" wrote:

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" wrote:

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" wrote:

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

Another 'Last cell in column' question
 

The formula in B2 is:

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

The formula in B1250 is:

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

Your statement Set foundit = Columns("B").Find("Sheet1", , xlValues,
xlWhole, xlByRows, xlPrevious) will find row 1250 as the last row.

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" wrote:

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

"Geoff K" wrote:

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" wrote:

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" wrote:

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

Another 'Last cell in column' question
 

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

"Compass Rose" wrote:

The formula in B2 is:

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

The formula in B1250 is:

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

Your statement Set foundit = Columns("B").Find("Sheet1", , xlValues,
xlWhole, xlByRows, xlPrevious) will find row 1250 as the last row.

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" wrote:

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

"Geoff K" wrote:

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" wrote:

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" wrote:

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