Locate last cell containing data in a row

  • Thread starter Thread starter Amr Tabbarah
  • Start date Start date
A

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
 
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.
Click to expand...


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
 
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)
Amr Tabbarah said:
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.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
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
 
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)
Amr Tabbarah said:
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.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top