[RESOLVED] Prevent drop down list from moving when column deleted.

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

---

if you put it in column j, will it move to the correct location?
can you add the drop down after the first 2 rows are removed?

Yes it will be in the correct position after columns are deleted, but not everyone will do it the same. I for instance delete the columns on a separate spreadsheet then paste into the working tab.

What about using find() to find a header name? Could I put that in?

Quote:

---

What about using find() to find a header name? Could I put that in?

---

of course
or last column with data

I controlled this with a conditional statement, which worked out well.