Auto Determine Range to pass to vlookup

**captlogic** · 08-17-2010, 01:44 PM

Hello all,

I'm trying to use vba to determine the range of a worksheet so that the range includes all data on the sheet.

I've found the following code to determine the bottom, right most cell

Please Login or Register  to view this content.

The problem is that the function returns a string, not a range reference.
i.e. if the 'ctrl-end' keystroke were to take me to cell 'AM68' the function returns a string
'$AM$68'.

It seems to me it should be a trivial process to turn the info into a range object, but I'm really struggling here. Any help would be appreciated.

Once I have the range I want to replace the hard-coded range reference is the code below

Please Login or Register  to view this content.

Thanks in advance for your help.

**BigBas** · 08-17-2010, 02:06 PM

Try adding this to your code:

Please Login or Register  to view this content.

Let me know if that helps.

**captlogic** · 08-17-2010, 02:38 PM

Originally Posted by BigBas

Try adding this to your code:

Please Login or Register  to view this content.

Let me know if that helps.

I knew it would be trivial to do what I wanted, but I figured it would take more than 1 line of code!

Your one line of code eliminated the need for the FindLastCell function!

Thanks