Passing Arguments
 

Hi

I have a routine that calls a function to determine the last row in a range.
I need the function to return the last row number to the calling routine
which will place the number in the active cell.

Hope someone can help.

Sub LastRow()
FindLastRow ("A")
ActiveCell.Formula = ???????
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
End Function

Many Thanks - Grant

Passing Arguments
 

Sub LastRow()
val = FindLastRow ("A")
ActiveCell.Formula = val
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
FindLastRow = LastRow
End Function

--
Regards,
Tom Ogilvy


"Grant Reid" wrote in message
...
Hi

I have a routine that calls a function to determine the last row in a
range.
I need the function to return the last row number to the calling routine
which will place the number in the active cell.

Hope someone can help.

Sub LastRow()
FindLastRow ("A")
ActiveCell.Formula = ???????
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
End Function

Many Thanks - Grant

Passing Arguments
 

can't use val as a variable - my typo - i meant it to be vVal

Sub LastRow()
Dim vVal as Long
vVal = FindLastRow ("A")
ActiveCell.Formula = vVal
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub LastRow()
val = FindLastRow ("A")
ActiveCell.Formula = val
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
FindLastRow = LastRow
End Function

--
Regards,
Tom Ogilvy


"Grant Reid" wrote in message
...
Hi

I have a routine that calls a function to determine the last row in a
range.
I need the function to return the last row number to the calling routine
which will place the number in the active cell.

Hope someone can help.

Sub LastRow()
FindLastRow ("A")
ActiveCell.Formula = ???????
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
End Function

Many Thanks - Grant

Passing Arguments
 

Grant,

Sub LastRow()
ActiveCell.Formula = FindLastRow("A")
End Sub

Public Function FindLastRow(ColLetter As String) As Long
FindLastRow = Range(ColLetter & Rows.Count).End(xlUp).Row
End Function

HTH,
Bernie
MS Excel MVP

"Grant Reid" wrote in message
...
Hi

I have a routine that calls a function to determine the last row in a
range.
I need the function to return the last row number to the calling routine
which will place the number in the active cell.

Hope someone can help.

Sub LastRow()
FindLastRow ("A")
ActiveCell.Formula = ???????
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
End Function

Many Thanks - Grant

Passing Arguments
 

Hi

To all who responded, many thanks for the help. Much appreciated.

Kind Regards - Grant

Passing Arguments
 

Hi

As a variation on my previous question. Assume that I have button on Sheet1.
I want to attach the LastRow macro to this button. What I'm attempting to
establish is to find the last row in a range but on a different sheet, so
I'll need to pass both the column letter and the sheet name.

How can the following code be modified to accomplish this?

Sub LastRow()
Dim val As Integer
val = FindLastRow("H")
ActiveCell.Formula = val
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
FindLastRow = LastRow
End Function

Many Thanks - Grant

Passing Arguments
 

Grant,

Like this:

Sub LastRow()
ActiveCell.Formula = FindLastRow("H","Sheet1")
End Sub

Public Function FindLastRow(cell As String, mySh As String) As String
FindLastRow = Worksheets(mySh).Range(cell & Rows.Count).End(xlUp).Row
End Function

HTH,
Bernie
MS Excel MVP

"Grant Reid" wrote in message
...
Hi

As a variation on my previous question. Assume that I have button on
Sheet1.
I want to attach the LastRow macro to this button. What I'm attempting to
establish is to find the last row in a range but on a different sheet, so
I'll need to pass both the column letter and the sheet name.

How can the following code be modified to accomplish this?

Sub LastRow()
Dim val As Integer
val = FindLastRow("H")
ActiveCell.Formula = val
End Sub

Public Function FindLastRow(cell) As String
Dim LastRow
LastRow = Range(cell & Rows.Count).End(xlUp).Row
FindLastRow = LastRow
End Function

Many Thanks - Grant

Passing Arguments
 

Hi

I need to apply a similar methodology to determine the last column in a
given worksheet. Once again, assume that I have button on Sheet1.

I want to attach the LastColumn macro to this button. What I'm attempting to
establish is to find the last column in a range but on a different sheet, so
I'll need to pass both the starting cell address and the sheet name eg;

Sub LastColumn
Dim strLastCol As String
strLastCol = FindLastCol("B10", "Sheet 1")
ActiveCell.Formula = strLastCol
End Sub

Its the called function FindLastCol, that I'm battling with. Can anyone help
fill in the blanks?

Public Function FindLastCol(??????????, mySh As String) As String
FindLastCol = Worksheets(mySh).??????????????
End Function

Many Thanks - Grant

Passing Arguments
 

Grant,

Assuming you want the column letters, like "AA":

Sub LastColumn()
Dim strLastCol As String
strLastCol = FindLastCol(10, "Sheet 1")
ActiveCell.Formula = strLastCol
End Sub

Public Function FindLastCol(myR As Long, mySh As String) As String
FindLastCol = ColLet(Worksheets(mySh).Cells(myR,
256).End(xlToLeft).Column)
End Function

Function ColLet(ColNum As Integer) As String
If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64)
ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65)
End Function


--
HTH,
Bernie
MS Excel MVP
"Grant Reid" wrote in message
...
Hi

I need to apply a similar methodology to determine the last column in a
given worksheet. Once again, assume that I have button on Sheet1.

I want to attach the LastColumn macro to this button. What I'm attempting
to
establish is to find the last column in a range but on a different sheet,
so
I'll need to pass both the starting cell address and the sheet name eg;

Sub LastColumn
Dim strLastCol As String
strLastCol = FindLastCol("B10", "Sheet 1")
ActiveCell.Formula = strLastCol
End Sub

Its the called function FindLastCol, that I'm battling with. Can anyone
help
fill in the blanks?

Public Function FindLastCol(??????????, mySh As String) As String
FindLastCol = Worksheets(mySh).??????????????
End Function

Many Thanks - Grant