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

Auto expanding Range
 

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



wrote in message
oups.com...
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

Auto expanding Range
 

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

Auto expanding Range
 

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?

Auto expanding Range
 

Sub addrow()

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







--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

Auto expanding Range
 

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



wrote in message
oups.com...
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?