Auto expanding Range

  • Thread starter Thread starter ire.kevin
  • Start date Start date
I

ire.kevin

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,

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
 
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
 
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
 
Back
Top