Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel VBA - Clearcontents in named ranges

Posted on 2013-01-11
6
Medium Priority
?
4,214 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
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Here is why.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

606 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