CODEWITHSUNDEEP
vbavalidationexcel
medavis6
medavis6

Reputation: 863

Data Validation Dynamic Range Moves Range Instead of Expanding Range

In Excel, I have a Data Validated range using the OFFSET() function that I'm hoping to dynamically add information to that I can then select in a drop down list. I have a VBA macro that I'm adding information to this list from and instead of expanding the list from $L$10:$L$230 to $L$10:$L$231, it shifts my list down to $L$11:$L$230. What am I doing incorrectly in my Named Range or Data Validation to not make this work? Or does it have something with using VBA to add to the range that causes it to work incorrectly?

"Rooms" in my Name Manager Refers To: 

=OFFSET(Sheet1!$L$10,0,0,COUNTA(Sheet1!$L:$L),1)

My Data Validation Drop Down Souce:

=Rooms

My "insert" Macro to add to the list:

Sub insert()
'
' insert Macro
'

'
    Range("A2:E2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("L10:P10").Select
    Selection.insert Shift:=xlDown
    Sheets("INSERT NEW ROOM").Select
    ActiveWindow.SmallScroll Down:=-18
    Range("A2").Select
End Sub

I also have a "Sort" VBA included in my Sheet1 for every time a new instance is added from the "INSERT NEW ROOM" tab.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("L9:L500")) Is Nothing Then
        Range("L9").Sort Key1:=Range("L10"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

Upvotes: 1

Views: 323

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

This happens because your insert macro changes the range your named range formula refers to, just like it would any normal formula.

The formula has a reference to cell $L$10. When you execute 

Range("L10:P10").Insert Shift:=xlDown

any formula, including the named range formula, that referes to a cell on or below row 10 will be updated to refer to a cell one row down (ie $L$11 in this case)

You can fix this by changing your named range formula to this

=OFFSET(Sheet1!$L$1,9,0,COUNTA(Sheet1!$L:$L),1)

Notice it now refers to cell $L$1 so is not affected by the insert.

Note:

You insert macro could do with some work

Try this instead

Sub InsertRooms() ' renamed to avoid using a built in function name
    Range("A2:E2").Copy
    Worksheets("Sheet1").Range("L10:P10").insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

Upvotes: 2

Related Questions