Search code examples
excelvbavalidationrange

Excel cell validation set by vba sets incorrect data range

I have a procedure which sets the validation list for cells in a range. I finally got it to work, getting the correct range to be set as validation list. But when I check the validation settings, the range is different. And I cannot understand what and why this happened.

Below is the messagebox telling me the range for the validation list:

correct validation list range

But below is what the validation list range is that has been set:

actual validation list range

I just do not understand why this range is changed to A2:A7 instead of a1:a6.

This is the code for the procedure:

Sub validatie()
Dim ws As Worksheet, ws1 As Worksheet
Set ws = ThisWorkbook.Worksheets("Hoofdbestand")
Set ws1 = ThisWorkbook.Worksheets("Verwijzingen")

aantalrijen2 = ws1.Range("A1", ws1.Range("A1").End(xlDown)).Cells.Count

With ws

aantalrijen = ws.Range("A1", ws.Range("A1").End(xlDown)).Cells.Count
    With .Range("B2:B" & aantalrijen).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & ws1.Name & "!" & "A1:A" & aantalrijen2
    MsgBox "=" & ws1.Name & "!" & "A1:A" & aantalrijen2
    End With
End With

End Sub
Solution

  • Lock the row references using $:

    Formula1:="=" & ws1.Name & "!" & "A$1:A$" & aantalrijen2

    Or just make the whole reference absolute:

    Formula1:="=" & ws1.Name & "!" & "$A$1:$A$" & aantalrijen2