Excel 2002: How to get the first and last reference in a column ?

[Mr. Low]

Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low

 

[Lorne]

Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first
and
last refrence in column A.

As long as there are no gaps in the column you could write a macro and
either assign it to a button that has to be pressed or write it for the
sheet and execute every time there is a change to any cell in column A:

Sub Macro1()
Range("A9").Select
Selection.End(xlDown).Select
Selection.Copy
Range("A8").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
End Sub

This assumes that the starting point is below cell A9 but not known.

 

[T. Valko]

If the data is text as it appears to be in your sample:

For the first item:

=INDEX(A10:A1000,MATCH("*",A10:A1000,0))

For the last item:

=LOOKUP("zzzzzzzzzz",A10:A1000)

Or:

=LOOKUP(REPT("z",255),A10:A1000)

 

[Shane Devenshire]

Hi,

The following returns the last non-blank cell

=LOOKUP(1,1/(A:A<>""),A:A)

You already have the best method for finding the first nonblank cell.

If this helps, please click the Yes button

cheers,
Shane Devenshire

 

[T. Valko]

=LOOKUP(1,1/(A:A<>""),A:A)

You can't use the entire column in the array in versions of Excel prior to
Excel 2007.

=LOOKUP(1,1/(A1:A65535<>""),A:A)

 

[Mr. Low]

Hi Valko,

Thanks for your formulas.

All works well.

Best Regards

Low

 

[Mr. Low]

Hi Shane,

Thanks for the formula.

I tested it, however it was not working in Excel 2002 as T. Valko said.

I have modified the formula to =LOOKUP(1,1/(A1:A65535<>""),A1:A65535 ) and
it works.

Thank you anyway for sharing with me.

Kind Regards

Low

 

[T. Valko]

You're welcome. Thanks for the feedback!