Locate last cell containing data in a row

[Amr Tabbarah]

I have a spreadsheet whereby each row contains data, but not in al
columns. I am interested in a formula, on each row, that would enabl
me to read the last cell (to the right) containing data.

Would appreciate any suggestion.

Thanks

 

[Ron Rosenfeld]

I have a spreadsheet whereby each row contains data, but not in all
columns. I am interested in a formula, on each row, that would enable
me to read the last cell (to the right) containing data.

Would appreciate any suggestion.

Thanks.

What kind of data?

If the data consists of numbers, then the array-entered formula:

=OFFSET(A3,0,-1+MAX(ISNUMBER(B3:IV3)*COLUMN(B3:IV3)))

in A3 will return the value in the rightmost cell containing data.

=OFFSET(A3,0,MAX(ISNUMBER(B3:IV3)*COLUMN(A3:IU3)))

is a bit simpler, but not as easy to understand.

If the data consists of text, then substitute ISTEXT for ISNUMBER in the above.

If the data is the result of a formula contained in those cells, then post back
with the formula, and the expected range of results.


--ron

 

[Don Guillett]

One way. Must be array entered (Ctrl+Shift+Enter). copy down
=OFFSET(A3,0,-1+MAX(ISNUMBER(3:3)*COLUMN(1:256)))

--
Don Guillett
SalesAid Software
(e-mail address removed)

I have a spreadsheet whereby each row contains data, but not in all
columns. I am interested in a formula, on each row, that would enable
me to read the last cell (to the right) containing data.

Would appreciate any suggestion.

Thanks.

creating financial statements

 

[David McRitchie]

Hi Amr,
Sorry I misread your request, Don Guillett providef the answer you were
looking for with a array formula to get the value of the last cell in a row.

But you or others may make use of my mistaken reading to locate
the cursor to the next empty cell to the right of the last used cell on a row.

I use a macro to go to the top of a column and one to go the bottom
of a column, which you can see on toolbars.htm page.

A few modifications and you have a macro to go
to the rightmost cell with data (or formulas). Have added OFFSET so
you go the empty cell to the right. You want to use a shortcut key or
a toolbar button, you can edit an arrow button and change the colors.

Sub GotoRightOfCurrentRow() 'D.McRitchie, 2003-12-08, misc, based on Tom Ogilvy
Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Select
End Sub

Similar macros are
'Documented in http://www.mvps.org/dmcritchie/excel/toolbars.htm
'Coding: http://www.mvps.org/dmcritchie/excel/code/gotostuff.txt

 

[Don Guillett]

Right click on the D at the top of col D>cut>move cursor to the A at the top
of col A>right click>click on insert CUT cells

--
Don Guillett
SalesAid Software
(e-mail address removed)

I have a spreadsheet whereby each row contains data, but not in all
columns. I am interested in a formula, on each row, that would enable
me to read the last cell (to the right) containing data.

Would appreciate any suggestion.

Thanks.

creating financial statements

 

[Don Guillett]

wrong thread. Thought I was answering another question. Oh well.


--
Don Guillett
SalesAid Software
(e-mail address removed)

Right click on the D at the top of col D>cut>move cursor to the A at the top
of col A>right click>click on insert CUT cells

--
Don Guillett
SalesAid Software
(e-mail address removed)

I have a spreadsheet whereby each row contains data, but not in all
columns. I am interested in a formula, on each row, that would enable
me to read the last cell (to the right) containing data.

Would appreciate any suggestion.

Thanks.


------------------------------------------------
[/url]


~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements