Prev: Conditional Formatting in 2007
Next: Can I stop the change event being triggered?
From: Jock on 27 Oct 2009 06:46
Code below is by Ron de Bruin and will find the last row with anything in it.

Function LastRow(Sh As Worksheet)
On Error Resume Next
LastRow = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

My sheet however has columns of formulae and this code treats them as being
valid data.
Can this be adapted to ignore all formulae and only recognise values?
Thanks,
--
Traa Dy Liooar

Jock
From: Jacob Skaria on 27 Oct 2009 07:15
Try the below...Modified the function to suit your requirement...

Function LastRow(Sh As Worksheet)
Dim varFound As Range
On Error Resume Next
Set varFound = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If varFound.HasFormula Then
Do: Set varFound = Sh.Cells.FindPrevious(varFound)
Loop Until varFound.HasFormula = False
End If
LastRow = varFound.Row
On Error GoTo 0
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Jock" wrote:

> Code below is by Ron de Bruin and will find the last row with anything in it.
>
> Function LastRow(Sh As Worksheet)
> On Error Resume Next
> LastRow = Sh.Cells.Find(What:="*", _
> After:=Sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlValues, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
>
> My sheet however has columns of formulae and this code treats them as being
> valid data.
> Can this be adapted to ignore all formulae and only recognise values?
> Thanks,
> --
> Traa Dy Liooar
>
> Jock
From: Rick Rothstein on 27 Oct 2009 12:04
Just a note on your modification and Ron's original code... you can omit the
After, LookAt and MatchCase arguments. If omitted, the default for the After
argument is the first cell in the range; since you are searching for *any*
character, it doesn't matter whether LookAt is set to all or part of the
text in the cell; and, again, since we are searching for *any* character, it
doesn't matter if that text is upper or lower case. So, the simplified
statement becomes this...

Set varFound = Sh.Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

--
Rick (MVP - Excel)


"Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message
news:F3255557-C359-4CE6-B5CE-6EC11546872C(a)microsoft.com...
> Try the below...Modified the function to suit your requirement...
>
> Function LastRow(Sh As Worksheet)
> Dim varFound As Range
> On Error Resume Next
> Set varFound = Sh.Cells.Find(What:="*", _
> After:=Sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlValues, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False)
> If varFound.HasFormula Then
> Do: Set varFound = Sh.Cells.FindPrevious(varFound)
> Loop Until varFound.HasFormula = False
> End If
> LastRow = varFound.Row
> On Error GoTo 0
> End Function
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Jock" wrote:
>
>> Code below is by Ron de Bruin and will find the last row with anything in
>> it.
>>
>> Function LastRow(Sh As Worksheet)
>> On Error Resume Next
>> LastRow = Sh.Cells.Find(What:="*", _
>> After:=Sh.Range("A1"), _
>> Lookat:=xlPart, _
>> LookIn:=xlValues, _
>> SearchOrder:=xlByRows, _
>> SearchDirection:=xlPrevious, _
>> MatchCase:=False).Row
>> On Error GoTo 0
>> End Function
>>
>> My sheet however has columns of formulae and this code treats them as
>> being
>> valid data.
>> Can this be adapted to ignore all formulae and only recognise values?
>> Thanks,
>> --
>> Traa Dy Liooar
>>
>> Jock

From: Jacob Skaria on 27 Oct 2009 13:13
Thanks Rick..I didnt bother to modify those..

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

> Just a note on your modification and Ron's original code... you can omit the
> After, LookAt and MatchCase arguments. If omitted, the default for the After
> argument is the first cell in the range; since you are searching for *any*
> character, it doesn't matter whether LookAt is set to all or part of the
> text in the cell; and, again, since we are searching for *any* character, it
> doesn't matter if that text is upper or lower case. So, the simplified
> statement becomes this...
>
> Set varFound = Sh.Cells.Find(What:="*", LookIn:=xlValues, _
> SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
>
> --
> Rick (MVP - Excel)
>
>
> "Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message
> news:F3255557-C359-4CE6-B5CE-6EC11546872C(a)microsoft.com...
> > Try the below...Modified the function to suit your requirement...
> >
> > Function LastRow(Sh As Worksheet)
> > Dim varFound As Range
> > On Error Resume Next
> > Set varFound = Sh.Cells.Find(What:="*", _
> > After:=Sh.Range("A1"), _
> > Lookat:=xlPart, _
> > LookIn:=xlValues, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlPrevious, _
> > MatchCase:=False)
> > If varFound.HasFormula Then
> > Do: Set varFound = Sh.Cells.FindPrevious(varFound)
> > Loop Until varFound.HasFormula = False
> > End If
> > LastRow = varFound.Row
> > On Error GoTo 0
> > End Function
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Jock" wrote:
> >
> >> Code below is by Ron de Bruin and will find the last row with anything in
> >> it.
> >>
> >> Function LastRow(Sh As Worksheet)
> >> On Error Resume Next
> >> LastRow = Sh.Cells.Find(What:="*", _
> >> After:=Sh.Range("A1"), _
> >> Lookat:=xlPart, _
> >> LookIn:=xlValues, _
> >> SearchOrder:=xlByRows, _
> >> SearchDirection:=xlPrevious, _
> >> MatchCase:=False).Row
> >> On Error GoTo 0
> >> End Function
> >>
> >> My sheet however has columns of formulae and this code treats them as
> >> being
> >> valid data.
> >> Can this be adapted to ignore all formulae and only recognise values?
> >> Thanks,
> >> --
> >> Traa Dy Liooar
> >>
> >> Jock
>
> .
>
 | 
Pages: 1
Prev: Conditional Formatting in 2007
Next: Can I stop the change event being triggered?