Find last value in a row or column

MrKowz

Well-known Member

Joined

Jun 30, 2008

Messages

6,653

Office Version

1. 365
2. 2016

Platform

1. Windows

• Jul 22, 2017

• #2

Welcome to the forums!

In VBA?

Try:

msgbox selection.cells(1,selection.columns.count).address

 

Last edited: Jul 22, 2017

Upvote 0

P

PeterBain

New Member

Joined

Jul 22, 2017

Messages

17

Office Version

1. 365

• Jul 22, 2017

• #3

MrKowz said:

Welcome to the forums!

In VBA?

Try:

msgbox selection.cells(1,selection.columns.count).address

Click to expand...

Thanks you for the quick reply. I was hoping there was a formula I could use rather than VBA

 

Upvote 0

P

PeterBain

New Member

Joined

Jul 22, 2017

Messages

17

Office Version

1. 365

• Jul 22, 2017

• #4

Thanks you for the quick reply. I was hoping there was a formula I could use rather than <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>

 

Upvote 0

MrKowz

Well-known Member

Joined

Jun 30, 2008

Messages

6,653

Office Version

1. 365
2. 2016

Platform

1. Windows

• Jul 22, 2017

• #5

Try:

=LOOKUP(2,1/(NOT(ISBLANK(1:1))),1:1)

This will return the value of the last populated column in row 1.

 

Upvote 0

P

PeterBain

New Member

Joined

Jul 22, 2017

Messages

17

Office Version

1. 365

• Jul 22, 2017

• #6

Thank You. Should I just change the row Reference (1:1) for each row?

This is what my data looks like. There is VLOOKUP formula in each cell getting the value. I need to get the last value

1	A	B	C	D	E	F	G	H
2	22/7/2017	29/7/2017	5/8/2017	12/8/2017	19/8/2017	26/8/2017	2/9/2017	Last Value
3	28	29		30		32
4	32	32	33	35	36	32	30
5	30	30

<colgroup><col width="25" style="width:19pt"> <col width="77" style="width:58pt" span="2"> <col width="69" style="width:52pt"> <col width="77" style="width:58pt" span="3"> <col width="69" style="width:52pt"> <col width="75" style="width:56pt"> </colgroup><tbody>
</tbody>

 

Upvote 0

K

ketxu

New Member

Joined

Jul 21, 2017

Messages

8

• Jul 22, 2017

• #7

H3 :

Code:

=LOOKUP(2;1/(ISNUMBER(A3:G3));A3:G3)

and fill down

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,175

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Jul 22, 2017

• #8

Another option...

Excel Workbook
	A	B	C	D	E	F	G	H
2	22/07/2017	29/07/2017	05/08/2017	12/08/2017	19/08/2017	26/08/2017	02/09/2017	Last Value
3	28	29		30		32		32
4	32	32	33	35	36	32	30	30
5	30	28						28
Sheet5

Excel 2010

Cell Formulas
Range	Formula
H3	=INDEX(A3:G3,MATCH(9.99999999999999E+307,A3:G3))
H4	=INDEX(A4:G4,MATCH(9.99999999999999E+307,A4:G4))
H5	=INDEX(A5:G5,MATCH(9.99999999999999E+307,A5:G5))

 

Upvote 0

P

PeterBain

New Member

Joined

Jul 22, 2017

Messages

17

Office Version

1. 365

• Jul 22, 2017

• #9

Thank you again.

That works. Except I had to change the ; to , otherwise it kept thinking it was text.

It works perfectly! Thanks for all your help.

 

Upvote 0

P

PeterBain

New Member

Joined

Jul 22, 2017

Messages

17

Office Version

1. 365

• Jul 22, 2017

• #10

Thanks you Mark858. Your formula works perfectly too.

I now have two great solutions.

I'm sure I'll be back with some more questions as I'm trying to build an Excel solution to a very manual process where users have used their discression in making decisions and now want it automated.

Thank you again.

Peter

 

Upvote 0