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