Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Last Row()
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Last Row()
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Last Row()
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Last Row()
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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming |