I have a spreadsheet that has a few drop down lists. The problem I will encounter is when data is pasted onto a tab, said data will fill columns A-J, columns A & B will be deleted, thus the last header will be in column H, as it should. My drop down code works, but moves when columns A and B are deleted. Is there a way to prevent this?


Code:
Sub CreateDV()
With Range("$H$2:$H$50").Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  xlBetween, Formula1:="Yes,No"
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = ""
  .InputMessage = ""
  .ErrorMessage = ""
  .ShowInput = True
  .ShowError = True
End With
End Sub