Finding the right most cell as a function

[Andrew]

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

 

[Andrew]

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

 

[Guest]

If you can select a cell in each row then:

Selection.End(xlToLeft).value

or (xlToRight)

should get you started

 

[Edwin Tam]

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
(e-mail address removed)
http://www.vonixx.com

 

[Andrew]

Edwin,

We've had a look at your functions, and are able to adapt them to our
requirements.


Thanks for your help


Andrew

Edwin Tam wrote in message ...
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
(e-mail address removed)
http://www.vonixx.com