Delete Named Ranges with Reference Errors (#REF!)--VBA

[zdodson]

Can someone point me in the correct direction? I am trying to delete named ranges with reference errors as a part of a larger sequence of VBA code. I was attempting to create a database that would:

1) Allow users to input information in a VBA userform (that has comboboxes),
2) Dynamically update the comboboxes in the userform by utilizing the RowSource property, and
3) Change what the RowSource referred to based on the named range that was assigned to it

Below is the code I was working with:

Private Sub cmdAddAFF_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("AFF")

Sheets("AFF").Select

[COLOR=#33cc00][B]'Unprotect worksheet[/B][/COLOR]
ActiveSheet.Unprotect

[B][COLOR=#33cc00]'Find first empty row in database[/COLOR][/B]
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

[COLOR=#33cc00][B]'Check for errors in data entry[/B][/COLOR]
If Trim(Me.cboTournName0.Value) = "" Then
    Me.cboTournName0.SetFocus
    MsgBox "Please select a tournament", vbCritical
    End If
    
If Trim(Me.txtTournDate0.Value) = "" Then
    Me.txtTournDate0.SetFocus
    MsgBox "Please enter the date of the tournament", vbCritical
    End If

[B][COLOR=#33cc00]'Copy the data to the Personnel database[/COLOR][/B]
ws.Cells(iRow, 1).Value = Me.cboTournName0.Value
ws.Cells(iRow, 2).Value = Me.txtTournDate0.Value
ws.Cells(iRow, 3).Value = Me.txtRound0.Value
ws.Cells(iRow, 4).Value = Me.cboSchoolName0.Value
ws.Cells(iRow, 5).Value = Me.cboCompName0.Value
ws.Cells(iRow, 6).Value = Me.txtPTDesc0.Value
ws.Cells(iRow, 7).Value = Me.txtPlanText.Value
ws.Cells(iRow, 8).Value = Me.txtAdv1A.Value
ws.Cells(iRow, 9).Value = Me.txtAdv1B.Value
ws.Cells(iRow, 10).Value = Me.txtAdv1C.Value
ws.Cells(iRow, 11).Value = Me.txtAdv1D.Value
ws.Cells(iRow, 12).Value = Me.txtAdv1E.Value
ws.Cells(iRow, 13).Value = Me.txtAdv2A.Value
ws.Cells(iRow, 14).Value = Me.txtAdv2B.Value
ws.Cells(iRow, 15).Value = Me.txtAdv2C.Value
ws.Cells(iRow, 16).Value = Me.txtAdv2D.Value
ws.Cells(iRow, 17).Value = Me.txtAdv2E.Value
ws.Cells(iRow, 18).Value = Me.txtAdv3A.Value
ws.Cells(iRow, 19).Value = Me.txtAdv3B.Value
ws.Cells(iRow, 20).Value = Me.txtAdv3C.Value
ws.Cells(iRow, 21).Value = Me.txtAdv3D.Value
ws.Cells(iRow, 22).Value = Me.txtAdv3E.Value
ws.Cells(iRow, 23).Value = Me.txtAdv4A.Value
ws.Cells(iRow, 24).Value = Me.txtAdv4B.Value
ws.Cells(iRow, 25).Value = Me.txtAdv4C.Value
ws.Cells(iRow, 26).Value = Me.txtAdv4D.Value
ws.Cells(iRow, 27).Value = Me.txtAdv4E.Value

[COLOR=#33cc00][B]'Clear the data from the userform[/B][/COLOR]
Me.cboTournName0.Value = ""
Me.txtTournDate0.Value = ""
Me.txtRound0.Value = ""
Me.cboSchoolName0.Value = ""
Me.cboCompName0.Value = ""
Me.txtPTDesc0.Value = ""
Me.txtPlanText.Value = ""
Me.txtAdv1A.Value = ""
Me.txtAdv1B.Value = ""
Me.txtAdv1C.Value = ""
Me.txtAdv1D.Value = ""
Me.txtAdv1E.Value = ""
Me.txtAdv2A.Value = ""
Me.txtAdv2B.Value = ""
Me.txtAdv2C.Value = ""
Me.txtAdv2D.Value = ""
Me.txtAdv2E.Value = ""
Me.txtAdv3A.Value = ""
Me.txtAdv3B.Value = ""
Me.txtAdv3C.Value = ""
Me.txtAdv3D.Value = ""
Me.txtAdv3E.Value = ""
Me.txtAdv4A.Value = ""
Me.txtAdv4B.Value = ""
Me.txtAdv4C.Value = ""
Me.txtAdv4D.Value = ""
Me.txtAdv4E.Value = ""

[B][COLOR=#33cc00]'Delete Previous School and Competitor Sheets from workbook[/COLOR][/B]
   [COLOR=#0000ff] [B]Sheets("School").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Competitor").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("AFF").Select
    Range("A2").Select[/B][/COLOR]
    
[B][COLOR=#33cc00]'Update School and Competitor Sheets[/COLOR][/B]
  Sheets("AFF").Select
    Columns("D:D").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "School"
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$14").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveWorkbook.Worksheets("School").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("School").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A1:A9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("School").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("AFF").Select
    Columns("E:E").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = "Competitor"
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$14").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveWorkbook.Worksheets("Competitor").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Competitor").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A1:A12"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Competitor").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("AFF").Select
    Range("A2").Select
End Sub

Right now I am getting a "Run-time error '9': Subscript out of range" error message. Once I try to debug the code and figure out where the error is, it is where I attempt to delete the sheets that once used a named range in it.

Any direction would be helpful on this! Thanks in advance!

P.S.

If you have any recommendations on "tightening" the code up a bit, let me know. I am new(er) to VBA, and I am willing to hear the suggestions.

 

[BiocideJ]

Something simple like this will delete any Named Ranges in the ActiveWorkbook that have a #REF! error in them.

For Each NR In ActiveWorkbook.Names
    If InStr(NR.Value, "#REF!") Then NR.Delete
Next

 

[zdodson]

Thanks for the reply. I did a lot of researching on this question and have used your solution and a few others, and they seem to work.

 

[BiocideJ]

Good deal. There are probably several dozen valid ways to go about this so whichever one is easiest for you is best. =)

 

[mburns_08109]

Just a heads-up...
When trying to delete some #REF!'d Names, I ran across the gotcha that you may not be able to do so successfully at all if the sheets are Locked (or at least not if ALL the sheets are locked).

(whether this affects all versions of Office/Excel or not, I can't speak to, though.)

So you may need to check that and unlock at least one or more sheets to delete these troublesome names (otherwise you may get 1004 errors from Excel - especially when driving this via OLE-Automation calls from a different host app (Access, Word, etc.).