Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lastrow - function not defined | Excel Discussion (Misc queries) | |||
Assistance with LASTROW function in VB | Excel Programming | |||
LastRow function - #VALUE | Excel Programming | |||
'LastRow' Function not working | Excel Programming | |||
Go to lastrow using other column's lastrow | Excel Programming |