XLUP maybe...

[Wes]

Hi all,

I'm needing to get the last value in a column (range L14 to L34) and then put that value into a specific cell (L7).

I tried the .xlup method but only had the cursor go to the last vacant cell in the range.

Any help would really be appreciated.

Thanks,
Wes

 

[dave3009]

Hi There

I looked up my desk reference and found

Nextrow = Application.WorksheetFunction.CountA(Range("A:A"))+1

Hope it helps


Dave

 

[Wes]

Thanks dave3009

I got her. I apparently was a little too hastey and add a bit too much to the string which made it stop at the first empty cell not the last one with information.
For future:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("L7") = Range("L34").End(xlUp)
End Sub

This is the end result.

Cheers

Wes

 

[Scott Huish]

Why VBA? How about a formula?
What kind of data is in the range L14 to L34, text or numeric?

If text:
In L7:
=LOOKUP(REPT("z",255),L14:L34)

If numeric,
In L7:
=LOOKUP(9.99999999999999E+307,L14:L34)

If mixed, perhaps:
In L7:
=LOOKUP(9.99999999999E+307,1/(L14:L34<>""),L14:L34)
Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Stormseed]

Why VBA? How about a formula?
What kind of data is in the range L14 to L34, text or numeric?

If text:
In L7:
=LOOKUP(REPT("z",255),L14:L34)

If numeric,
In L7:
=LOOKUP(9.99999999999999E+307,L14:L34)

If mixed, perhaps:
In L7:
=LOOKUP(9.99999999999E+307,1/(L14:L34<>""),L14:L34)
Confirm with CTRL-SHIFT-ENTER rather than just Enter

Excellent ! thanks for the info