[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3458
  • Last Modified:

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
0
mike637
Asked:
mike637
1 Solution
 
Anthony BerenguelCommented:
range("name_of_NamedRange").ClearContents
0
 
Anthony BerenguelCommented:
You might have to unprotect the cells in the namedRanges before clearing the contents of them.
0
 
Shanan212Commented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
redmondbCommented:
Hi, mike637.

Sub Clear_All_Sheet1_Name_Ranges()
Dim xRange    As Range
Dim xName     As name
Dim xResponse As Long

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

Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
0
 
mike637Author Commented:
Great, Brian - this cleared all the ranges perfectly.


Thanks again,
Michael
0
 
redmondbCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now