As you can see from the macro below (also located in Sheet1 of the attached spreadsheet), I am trying to create and then delete specific named ranges. I will add more code between the creation and deletion sections after I get this part of the code to work. Unfortunately, the macro is not deleting the most recently defined named range. If Group5 is the last named range, the macro will only delete the Group5 named range every other time I run the macro. If I create Group5 on the first run of the macro, the macro does not delete Group5. The second time I run the macro, the macro does delete Group5. The third time I run the macro, Group5 is not deleted. The fourth time it is deleted, etc. Does anyone have suggestions regarding what code I am missing to make my macro run correctly?
Sub Dashboard()
Dim Group1 As Name
Dim Group2 As Name
Dim Group3 As Name
Dim Group4 As Name
Dim Group5 As Name
Dim Group6 As Name
Set Group1 = Nothing
Set Group2 = Nothing
Set Group3 = Nothing
Set Group4 = Nothing
Set Group5 = Nothing
Set Group6 = Nothing
For Each c In Sheet1.Range("DashboardCheckboxes").Cells
If c.Value = True Then
c.Offset(1, 1).Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.Column)).Select
On Error Resume Next
Set Group1 = Sheet1.Range("Group1").Name
Set Group2 = Sheet1.Range("Group2").Name
Set Group3 = Sheet1.Range("Group3").Name
Set Group4 = Sheet1.Range("Group4").Name
Set Group5 = Sheet1.Range("Group5").Name
Set Group6 = Sheet1.Range("Group6").Name
On Error GoTo 0
If Group1 Is Nothing Then
Selection.Name = "Group1"
ElseIf Group2 Is Nothing Then
Selection.Name = "Group2"
ElseIf Group3 Is Nothing Then
Selection.Name = "Group3"
ElseIf Group4 Is Nothing Then
Selection.Name = "Group4"
ElseIf Group5 Is Nothing Then
Selection.Name = "Group5"
ElseIf Group6 Is Nothing Then
Selection.Name = "Group6"
End If
End If
Next
On Error GoTo 0
If Not Group1 Is Nothing Then
ActiveWorkbook.Names("Group1").Delete
End If
If Not Group2 Is Nothing Then
ActiveWorkbook.Names("Group2").Delete
End If
If Not Group3 Is Nothing Then
ActiveWorkbook.Names("Group3").Delete
End If
If Not Group4 Is Nothing Then
ActiveWorkbook.Names("Group4").Delete
End If
If Not Group5 Is Nothing Then
ActiveWorkbook.Names("Group5").Delete
End If
If Not Group6 Is Nothing Then
ActiveWorkbook.Names("Group6").Delete
End If
End Sub
Bookmarks