Fetch the last value of a range of cells

Question

I would like to (dynamically) fetch the last value of a range. Therefore I have the following code.

Range("C13").Select
Range(Selection, Selection.End(xlDown)).Select
x1 = Selection.End(xlDown).Select
MsgBox (x1)

This however gives TRUE in stead of what I am looking for (the actual value of the cell). Any thoughts on what I should do to get the value?

Possible duplicate of [Error in finding last used cell in VBA](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Comintern, Jul 25 '16 at 18:06

score 2 · Answer 1 · answered Jul 25 '16 at 18:26

2

Replace all your lines with this one:

MsgBox Cells(Rows.Count,3).End(xlUp).Value

it will return the value of the last cell in column C that has a value.

answered Jul 25 '16 at 18:26

Scott Craner

148,073
10
49
81

score 1 · Answer 2 · 2016-07-26T12:35:48.030

From Ron DeBruin's site. I use this all the time. This is a more reliable way to get the last Row, Column, or cell in a range. using xldown to get to the last row is that with xldown, it will stop at the first blank cell. .usedrange will catch any ghost formatting that's on the sheet, and can give unpredictable results

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

Then call it like this:

sub get_the_Last()
dim TheValue as string
dim myRange as range

    set myRange = (assign your range here)
    TheValue = range(Last(3, MyRange)).value 

end sub

It will work, but it's hugely over-complicated for such a simple task. — SierraOscar, Jul 25 '16 at 19:45
@MacroMan I use that "Last" function all the time. It's not too complicated once it's loaded in, just a simple function call, and it ALWAYS gives the last cell with data — , Jul 25 '16 at 20:48

score -3 · Answer 3 · edited Jul 25 '16 at 19:42

-3

This is how I would have done it

Sub Test()

Dim wb As Workbook
Dim ws As Worksheet

Dim x1 As Variant

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

ws.Range("C13").Select

x1 = Selection.Rows(Selection.Rows.Count).Value

MsgBox (x1)

End Sub

So Selection.Rows.Count returns the amount of rows that exist in the selected range.

Alternatively:

Sub Test1()

Dim wb As Workbook
Dim ws As Worksheet

Dim x1 As Variant

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

ws.Range("C13").Select

MsgBox Selection.Rows(Selection.Rows.Count).Value

End Sub

edited Jul 25 '16 at 19:42

SierraOscar

17,507
6
40
68

answered Jul 25 '16 at 18:20

Henrik

23
1
7

This will *always* give the value of "C13", and since you only select 1 cell, `Selection.Rows.Count` will *always* be 1. – Comintern Jul 25 '16 at 18:24
Yes of cource it will allways return the value of cell C13. But I haven´t interpreted the question as how do I make a dynamic range? – Henrik Jul 25 '16 at 18:27
Read the question again. The OP is asking how to get the value of from the *last row in the column that contains the selection*. – Comintern Jul 25 '16 at 18:29
Yes, so if he change what range is selected it will still work. It will allways return the value of the last cell in the selected range. – Henrik Jul 25 '16 at 18:31
How do you propose that he determine what range to select if he doesn't know that the last row is? – Comintern Jul 25 '16 at 18:32
I repeat, the question was not how he determine what range he will be using. The question is based on that he has a dynamic range form which he want the last rows value. If he need help with determening a dynamic range aswell, that is fine. But that was not what he asked for. – Henrik Jul 25 '16 at 18:37
I guess the point of `Range(Selection, Selection.End(xlDown)).Select` eludes me then. – Comintern Jul 25 '16 at 18:38
My last comment now, this adds nothing to the thread. What Range(Selection, Selection.End(xlDown)).Select would do is select all rows from the previous selected range. I presume OP intended to select the last row in the previously selected range. – Henrik Jul 25 '16 at 18:48
@Henrik that is complete nonsense, you clearly have no understanding of the `xlDirection` enumeration or what the OP is trying to achieve. – SierraOscar Jul 25 '16 at 19:50
Also `But I haven´t interpreted the question as how do I make a dynamic range?` - the question clearly states: _(dynamically)_ – SierraOscar Jul 25 '16 at 19:53

Fetch the last value of a range of cells

3 Answers3