Results 1 to 5 of 5

VBA Macro Does not Delete Most Recent Defined Named Range

Threaded View

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    11

    VBA Macro Does not Delete Most Recent Defined Named Range

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1