Instead, I would use d defined name for your validationlist and use that
name
insertnamedefinename itvallist
in the refers to box use an offset formula such as
=offset($a$1,0,0,counta($a:$a),1)
if blanks use something besides counta
Now, your list will be self adjusting.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Munchkin" wrote in message
...
I need my macro to reset the data validation for D6 each time it is run as
the range of rows will increase as new info is added. How can I do this?

Range("D6").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$A$1:$A$547"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With