How do I retrieve the last value in a column, when the column has a variable number of rows?
I figured how to get the row number, e.g.:
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
How do I use that to get the value in the cell?
e.g. if lastRow = 8794, I want to get the value in cell K8794.
try
range("K" & lastRow).value
after you get a value for lastRow
Solution to retrieve the last value in a column, from the comment left by @ScottCraner:
Cells(Rows.Count, "K").End(xlUp).Value
Just to throw another alternative out there - you can use a Range variable to get the cell, then from there you can easily grab the .Row or .Value (or any other Range property):
Dim lastCell as Range
Set lastCell = Sheets("Sheet1").Cells(Rows.Count,"K").End(xlUp)
Debug.Print "Last cell's row is: " & lastCell.Row
Debug.Print "Last cell's value is: " & lastCell.Value
Debug.Print "Last cell's address is: " & lastCell.Address
There are number of ways you can get the value of last cell with a value in column K...
Dim lastRow As Long
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
MsgBox Range("K" & lastRow).Value
'OR
MsgBox Cells(lastRow, "K").Value
'OR
MsgBox Cells(lastRow, 11).Value 'here 11 is the column index for column K
Use this:
Sub ok()
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
Debug.Print Cells(lastRow, 11).Value
End Sub
Cells(Rows.Count, "K").End(xlUp).Value