asked on

Excel VBA - Clearcontents in named ranges

Hello Experts,

In my code to clear the workbook for a new period - I need to clear the contents of all named ranges on sheet12. (all cells are locked)

How can I have it clear the contents of each cell in the named range on sheet12. There are 42 named ranges. I do not want to clear the name of the range. I still need those.

Thanks,
Michael

Anthony Berenguel

range("name_of_NamedRange").ClearContents

Anthony Berenguel

You might have to unprotect the cells in the namedRanges before clearing the contents of them.

Shanan212

Range("YOUR-RANGE-NAME").ClearContents

Open in new window

Unprotecting it

    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect "YOUR-PASSWORD"
    End If

Open in new window

protecting it again

ActiveSheet.protect "YOUR-PASSWORD"

Open in new window

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mike637

ASKER

Great, Brian - this cleared all the ranges perfectly.

Thanks again,
Michael

redmondb

Thanks, Michael.

I just noticed the bit about the cells being locked. The version below will turn off the sheet's protection while the ranges are being cleared...

Sub Clear_All_Sheet1_Name_Ranges()
Dim xRange     As Range
Dim xName      As name
Dim xResponse  As Long
Dim xPassword  As String
Dim xProtected As Boolean

xResponse = MsgBox("About to clear all Sheet1's Name Ranges in " & ActiveWorkbook.name _
            & Chr(10) & "('OK' to Delete, 'Cancel' to Quit.)", vbOKCancel, "Clear_All_Sheet1_Name_Ranges")
If xResponse = 2 Then
    MsgBox ("User chose to cancel - run terminating.")
    Exit Sub
End If

xPassword = "Fred"

If Sheets("Sheet1").ProtectContents Then
    xProtected = True
    ActiveSheet.Unprotect Password:=xPassword
End If
    
For Each xName In ActiveWorkbook.Names
    
    Set xRange = Nothing
    
    On Error Resume Next
        Set xRange = Intersect(Sheets("Sheet1").UsedRange, xName.RefersToRange)
    On Error GoTo 0
    
    If Not xRange Is Nothing Then xRange.ClearContents
    Debug.Print xRange.Address

Next xName

If xProtected Then Sheets("Sheet1").Protect Password:=xPassword

Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.