[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
Re: Prevent drop down list from moving when column deleted.
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?
Re: Prevent drop down list from moving when column deleted.
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?
Re: Prevent drop down list from moving when column deleted.
Quote:
What about using find() to find a header name? Could I put that in?
of course
or last column with data
Re: Prevent drop down list from moving when column deleted.
I controlled this with a conditional statement, which worked out well.