lastrow function

[okrob]

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

 

[Ron de Bruin]

Hi okrob

For a column use

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

 

[okrob]

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...

 

[Guest]

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

 

[Ron de Bruin]

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