lastrow function
 

Given this function:

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

How would I go about changing this to a specific column instead of the
entire worksheet?

Example of how it's called now:
Sheet7.Range("D" & LastRowByRow(Sheet7) + 1).Value = NewEntry

lastrow function
 

Hi okrob

For a column use

Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"okrob" wrote in message ups.com...
Given this function:

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

How would I go about changing this to a specific column instead of the
entire worksheet?

Example of how it's called now:
Sheet7.Range("D" & LastRowByRow(Sheet7) + 1).Value = NewEntry

lastrow function
 

On Feb 16, 10:35 am, "okrob" wrote:
Given this function:

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

How would I go about changing this to a specific column instead of the
entire worksheet?

Example of how it's called now:
Sheet7.Range("D" & LastRowByRow(Sheet7) + 1).Value = NewEntry

I can use a variable instead of a function and set the variable to
lastrowbyrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
then call it up with
Sheet7.Range("D" & lastrowbyrow + 1).Value = NewEntry
BUT, I need to use this everywhere and would like a function to do
it...

lastrow function
 

I don't know if you can go this route or not but here goes
I create a variable to use the column
Modify the function to take both the worksheet and the column as arguments
Modify the function in 3 places
one a new variable to build the After range. It didn't seem to let me do it
in place
two in the first Range statement
three in the After line

it seems to work for me,
hope it helps

David


Sub FindTest()
Dim MyA As Worksheet
Dim MyC As String
Set MyA = Sheets("Sheet1")
MyC = "C"
MyB = LastRowByRow(MyA, MyC)
MsgBox MyB

End Sub


Function LastRowByRow(sh As Worksheet, MyCol As String)
Dim MyAfter As String
On Error Resume Next
MyAfter = MyCol & "1"
LastRowByRow = sh.Range(MyCol & ":" & MyCol).Find(What:="*", _
After:=sh.Range(MyAfter), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


"okrob" wrote:

On Feb 16, 10:35 am, "okrob" wrote:
Given this function:

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

How would I go about changing this to a specific column instead of the
entire worksheet?

Example of how it's called now:
Sheet7.Range("D" & LastRowByRow(Sheet7) + 1).Value = NewEntry

I can use a variable instead of a function and set the variable to
lastrowbyrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
then call it up with
Sheet7.Range("D" & lastrowbyrow + 1).Value = NewEntry
BUT, I need to use this everywhere and would like a function to do
it...

lastrow function
 

Add a new page to my site today
http://www.rondebruin.nl/last.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"okrob" wrote in message oups.com...
On Feb 16, 10:35 am, "okrob" wrote:
Given this function:

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

How would I go about changing this to a specific column instead of the
entire worksheet?

Example of how it's called now:
Sheet7.Range("D" & LastRowByRow(Sheet7) + 1).Value = NewEntry

I can use a variable instead of a function and set the variable to
lastrowbyrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
then call it up with
Sheet7.Range("D" & lastrowbyrow + 1).Value = NewEntry
BUT, I need to use this everywhere and would like a function to do
it...