Q: ActiveWorkbook.Names.Add generates error "The syntax of this name isn't correct." This code has been working, now it is giving me:

Run-time error '1004'
  The syntax of this name isn't correct.


I think after the script runs I need to remove this saved name, however, I do not know how to do this.
If after the debug error I start the code at the beginning of the with statement it works.
ActiveWorkbook.Names.Add Name:="MyList1", RefersTo:="=FlexInfo[Card Holder]"
'RefersTo:="=TableName[ColumnHeader]"

With Range("CardHold").Validation
     .Delete
     .Add Type:=xlValidateList, Formula1:="=MyList1" '<Errors here
     .ShowError = False
End With


A: Looks like the either 


*

*Name range (MyList1) already exists in the Excel workbook. Try deleting it before adding it again. 

*the space between Card & Holder in the another name range "FlexInfo[Card Holder]" is causing the issue.



On Error Resume Next
    ActiveWorkbook.Names("MyList1").Delete
    ActiveWorkbook.Names("FlexInfo[Card Holder]").Name = "FlexInfo[CardHolder]"
On Error GoTo 0

ActiveWorkbook.Names.Add Name:="MyList1", RefersTo:="=FlexInfo[CardHolder]"
'RefersTo:="=TableName[ColumnHeader]"

With Range("CardHold").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=MyList1" '<Errors here
    .ShowError = False
End With

