Auto expanding Range

What I have is a drop down menu, I want the input range to grow as I
add data to my table, without having to go back in and redefining the
range everytime.

It's telling me that my variable is not defined.

I very new to VBA, where am I going wrong?

Sub addrow()
bottom = Cells(65536, 23).End(xlUp).Row
Range("$W$19,bottom").Select
ActiveWorkbook.Names.Add Name:="enginetypes"
End Sub

I figured outthe Variable, the Range is returned the "yellow highlight"

Sub addrow()
Dim bottom
bottom = Cells(65536, 23).End(xlUp).Row
Range("$W$19,bottom").Select
ActiveWorkbook.Names.Add Name:="enginetypes"
End Sub

Hi Kevin,

Try:

Sub addrow()
Dim bottom As Long

bottom = Cells(65536, 23).End(xlUp).Row
ActiveWorkbook.Names.Add _
Name:="enginetypes", _
RefersTo:=Range("W19:W" & bottom)
End Sub


---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
> What I have is a drop down menu, I want the input range to grow as I
> add data to my table, without having to go back in and redefining the
> range everytime.
>
> It's telling me that my variable is not defined.
>
> I very new to VBA, where am I going wrong?
>
> Sub addrow()
> bottom = Cells(65536, 23).End(xlUp).Row
> Range("$W$19,bottom").Select
> ActiveWorkbook.Names.Add Name:="enginetypes"
> End Sub
>

You my friend, are a GENIUS.

Just to go slightly further. I have it running the Macro on Open, how
would i get the Macro to run upon adding data to the next row?

I think I will make a forum eventually to run the Macro and add the
data, but for kicks...how would I?

Sub addrow()

Range("$W$19:W" & _
Cells(65536, 23).End(xlUp).Row _
).Name:="enginetypes"
End Sub







--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> What I have is a drop down menu, I want the input range to grow as I
> add data to my table, without having to go back in and redefining the
> range everytime.
>
> It's telling me that my variable is not defined.
>
> I very new to VBA, where am I going wrong?
>
> Sub addrow()
> bottom = Cells(65536, 23).End(xlUp).Row
> Range("$W$19,bottom").Select
> ActiveWorkbook.Names.Add Name:="enginetypes"
> End Sub
>

Hi Kevin,

> Just to go slightly further. I have it running the Macro on Open, how
> would i get the Macro to run upon adding data to the next row?

Rather than adopting a VBA approach, why not use a dynamic range?

If you are not familiar with the use of dynamic ranges, visit Debra
Dalgleish's Dynanic Ranges page at:

http://www.contextures.com/xlNames01.html#Dynamic


---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
> You my friend, are a GENIUS.
>
> Just to go slightly further. I have it running the Macro on Open, how
> would i get the Macro to run upon adding data to the next row?
>
> I think I will make a forum eventually to run the Macro and add the
> data, but for kicks...how would I?
>