Last used row in a column containing a cell.

[sts023]

Hi again....

I've a horrible feeling I'm getting way too complex in a solution to a problem, and was wondering if I'm missing something blindingly obvious.

On a given Worksheet, I have a cell address (e.g. "B24", with no dollar signs)
I need a way to find the last used row in the column in which the target cell is.

Here's the code (which works) but I'm sure there's a better way.
Any ideas?

Public Sub SampleCode()
Dim lngLastRow                  As Long
Dim lngStartRow                 As Long
Dim strCol                      As String
Dim strStartCell                As String
Dim strWks                      As String
Dim wks                         As Excel.Worksheet
'*
'** Get addressability to the Worksheet and Cell.
'*
  strWks = "Sheet1"
  strStartCell = "B24"
  Set wks = Worksheets(strWks)
  wks.Select
  wks.Range(strStartCell).Select
'*
'** Because I only know how to get the last
'** row in a column using
'** e.g. Range("Z12345").End(xlUp), I
'** need to change the column NUMBER to
'** a column LETTER.
'*
  strCol = funColAsLetter(Selection.Column)
'*
'** Get ROW of start CELL.
'
  lngStartRow = Selection.Row
'*
'** Get ROW of last used CELL in the
'** column containing the target CELL.
'*
  lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
End Sub      'funGetDateInCol


Public Function funColAsLetter(intCol As Integer) As String
Dim intA1                       As Integer
Dim intA2                       As Integer
Dim intRemainder                As Integer
  Select Case intCol
    Case 0
      funColAsLetter = ""
    Case 1 To 26
      funColAsLetter = Chr(intCol + 64)
    Case Else
      intA1 = Int(intCol / 27)
      intA2 = intCol - (intA1 * 26)
      funColAsLetter = Chr(intA1 + 64) & _
                         Chr(intA2 + 64)
  End Select
End Function 'funColAsLetter

I

 

[BobUmlas]

Given you know this:
lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row

you can use
Answer = Cells(lngLastRow,2).address(0,0)
and if lngLastRow is 34, then Answer is "B34" (no quotes).

 

[Jonmo1]

You don't need to convert column Number to Letter.
You can use the column Number just as well..

The Cells property does pretty much everything the Range property does.
But you can use column NUMBER in Cells
Cells(Row#, ColumnNumber OR Letter)

So we can do
Cells(5,7) : this is the same as Range("G5")
Cells(5,"G") : this is also the same as Range("G5")


So something like this.

strStartCell = "B24"
Set MyRange = Range(strStartCell)
MyCol = MyRange.Column
LastRow = Cells(Rows.Count, MyCol).End(xlup).Row

 

[sts023]

Thanks Jonmo1 (and all others)
I don't know if its heartrending or heartwarming to see an elegant three line solution to something I've spent a whole day trying to figure out!
Oh well, onwards and upwards.....

 

[Jonmo1]

You're welcome.