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?
I
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?
Code:
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