Last value in any column

Merlinwarrior · Dec 4, 2018

Good day,
I need either a function or macro that will give me the last numeric value of any column of data. I have read the threads related to "=LOOKUP(2,1/(A:A<>""),A:A)."
However the above limits me to column A and I don't have the skills to change it to give me what I need from other columns.

The purpose of use is for a dashboard. I need to extract totals from many spreadsheets every day, to just one sheet with the = function in cells on that dashboard. The intention is to paste the volumes of data into many tabs and then just pull the totals to the one dashboard sheet.

Apologies for the long explanation of the purpose.

Thank you!

James006 · Dec 4, 2018

Hello,

When you are saying

last numeric value of any column of data

Do you mean last numeric value in ALL your Columns ...?

Aladin Akyurek · Dec 4, 2018

Last numeric value...

=LOOKUP(9.99999999999999E+307,A:A)

not the one you mention.

Merlinwarrior · Dec 4, 2018

It worked!
Thank you very much Aladin.

Aladin Akyurek said:
Last numeric value...

=LOOKUP(9.99999999999999E+307,A:A)

not the one you mention.

Merlinwarrior · Dec 4, 2018

Thanks James. Just 1 column per spreadsheet.

Kamolga · Dec 4, 2018

By naming the range or using table, you can get to the last column.

Else there is a way to use a udf like this one

Code:

Function LastNum()
Dim LastColumn As Long
Dim LastRow As Long
Dim Ctr As Long
    With ActiveSheet
        LastColumn = .UsedRange.Columns.Count
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
    End With
    For Ctr = LastRow To 1 Step -1
        If IsNumeric(Cells(Ctr, LastColumn).Value) = True Then
            LastNum = Cells(Ctr, LastColumn).Value
            Exit Function
        End If
    Next Ctr
End Function

and then typing this is in sheet

Code:

=LastNum()

strooman · Dec 4, 2018

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][/tr][tr][td]

1

[/td][td="bgcolor:#0070C0"]Data[/td][td="bgcolor:#0070C0"]Result[/td][td="bgcolor:#0070C0"]Formula[/td][td="bgcolor:#0070C0"]Task[/td][/tr]
[tr][td]

2

[/td][td]

1

[/td][td]

99

[/td][td] =LOOKUP(9,99999999999999E+307;A2:A14)[/td][td]Get value last number[/td][/tr]
[tr][td]

3

[/td][td]

300

[/td][td]

Peter

[/td][td] =INDEX(A2:A14;MATCH(REPT("z";255);A2:A14))[/td][td]Get value last tekst[/td][/tr]
[tr][td]

4

[/td][td]

400

[/td][td]

99

[/td][td] =INDEX(A2:A14;MATCH(9,99999999999999E+307;A2:A14))[/td][td]Get value last number[/td][/tr]
[tr][td]

5

[/td][td]

500

[/td][td]

Peter

[/td][td] =LOOKUP(2;1/(LEN(A2:A14)>0);A2:A14)[/td][td]Get value last tekst/number[/td][/tr]
[tr][td]

6

[/td][td]

Chantal

[/td][td]

Peter

[/td][td] =LOOKUP(REPT("z";255);A2:A14)[/td][td]Get value last tekst[/td][/tr]
[tr][td]

7

[/td][td]

1,00E+307