?
Solved

Excel VBA - Clearcontents in named ranges

Posted on 2013-01-11
6
Medium Priority
?
2,730 Views
Last Modified: 2013-01-11
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
Comment
Question by:mike637
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 38767993
range("name_of_NamedRange").ClearContents
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 38768001
You might have to unprotect the cells in the namedRanges before clearing the contents of them.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38768003
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
Technology Partners: 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!

 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38768051
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
 

Author Closing Comment

by:mike637
ID: 38768065
Great, Brian - this cleared all the ranges perfectly.


Thanks again,
Michael
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38768074
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question