CODEWITHSUNDEEP
excelvba
Ashley
Ashley

Reputation: 83

How do I get the last value in a column?

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.

Upvotes: 3

Views: 15088

Answers (5)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

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

Upvotes: 1

Ashley
Ashley

Reputation: 83

Solution to retrieve the last value in a column, from the comment left by @ScottCraner:

Cells(Rows.Count, "K").End(xlUp).Value

Upvotes: 3

BruceWayne
BruceWayne

Reputation: 23285

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

Upvotes: 2

ChipperG
ChipperG

Reputation: 64

try 

range("K" & lastRow).value

after you get a value for lastRow

Upvotes: 3

pokemon_Man
pokemon_Man

Reputation: 902

Use this:

Sub ok()
    lastRow = Cells(Rows.Count, "K").End(xlUp).Row
    Debug.Print Cells(lastRow, 11).Value
End Sub

Upvotes: 0

Related Questions