# XLUP maybe...

#### Wes

##### Board Regular
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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### dave3009

##### Well-known Member
Hi There

I looked up my desk reference and found

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

Hope it helps

Dave

#### Wes

##### Board Regular
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

##### MrExcel MVP
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

##### Banned
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

Replies
2
Views
493
Replies
3
Views
590
Replies
7
Views
411
Replies
0
Views
160
Replies
1
Views
376

1,191,120
Messages
5,984,762
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back