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,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,
> > > 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
|
Similar Threads
