Ladies & Gents,

I'm using a spread sheet which has many rows of information, with multiple
start & finishing columns. Can anyone help me with a function that can
return the first & last values of each row?


TIA

Andrew Sbrana

Ladies & Gents,

I'm using a spreadsheet which has many rows of information, with multiple
start & finishing columns. Can anyone help me with a function that can
return the first & last values of each row?

I forgot to mention that I'm using Excel 97 to create the spreadsheet
mentioned.


TIA

Andrew Sbrana

If you can select a cell in each row then:

Selection.End(xlToLeft).value

or (xlToRight)

should get you started


"Andrew" wrote:

Ladies & Gents,

I'm using a spreadsheet which has many rows of information, with multiple
start & finishing columns. Can anyone help me with a function that can
return the first & last values of each row?

I forgot to mention that I'm using Excel 97 to create the spreadsheet
mentioned.


TIA

Andrew Sbrana

Your question is unclear and I cannot give a complete answer.

1) What did you meant by "has multiple start & finish columns"? Every row's
start column and finish column may be different?

2) If you got the custom functions, where will you enter those functions?
Will they be entered in the same column on the right-hand-side, or adjacent
to the last column of each row?

The optimal case will be:
1) All row start in column A
2) The formulas are entered on the right-hand-side of each row, and there is
always one empty cell between the right-most cell and the formula. (Because
if there is no empty cell, the formulas will become the last and second-last
columns!!)

The follow macros may give you some hint of the basic strategy to solve the
problem:

Function firstcell()
firstcell = _
Application.Caller.Parent.Rows(Application.Caller. Row). _
Cells(1).Value
End Function

Function lastcell()
lastcell = _
Application.Caller.Parent.Rows(Application.Caller. Row). _
Cells(1).End(xlToRight).Value
End Function



Regards,
Edwin Tam

http://www.vonixx.com



On 1/11/05 6:36 AM, in article ,
"Andrew" wrote:

Ladies & Gents,

I'm using a spread sheet which has many rows of information, with multiple
start & finishing columns. Can anyone help me with a function that can
return the first & last values of each row?


TIA

Andrew Sbrana