ClearContents

[buzz71023]

I need to clear contents of several different ranges with a loop. I've done some research but cant figure out how to do it. All the ranges that need to be cleared are named ranges (JobSpecs, JobInfo, JobTest, EmailAddress). I know this is simple but I cant seem to get it right. Can someone help me figure this out? Thanks

 

[BiocideJ]

I need to clear contents of several different ranges with a loop. I've done some research but cant figure out how to do it. All the ranges that need to be cleared are named ranges (JobSpecs, JobInfo, JobTest, EmailAddress). I know this is simple but I cant seem to get it right. Can someone help me figure this out? Thanks

Range("JobSpecs").ClearContents
(repeat for each of your named ranges.)

Or if you want to do them all at once...

Range("JobSpecs, JobInfo, JobTest, EmailAddress").ClearContents

 

[Warship]

Something like:

Sub ClearContentsArray()
    Dim arrCells, c
    arrCells = Array("JobSpecs", "JobInfo", "JobTest", "EmailAddress")
    For Each c In arrCells
        Range(c).ClearContents
    Next
End Sub

 

[buzz71023]

Warship, the code works perfect. Thanks for you help!
What if I have a merged cell and I get the runtime 1004 cannot changed part of a merge cell error. i ask this because I will eventually have a merged cell within the code and have had the issue before with that specific error.

 

[buzz71023]

BiocideJ, Thanks for your help as well. I know how to clear ranged individually but I am trying to learn how to loop codes and this was a good example for me to start off with... Again thanks for your reply.

 

[Warship]

What if I have a merged cell...

You'll need to UnMerge then re-Merge:

Sub ClearContentsArrayOnMerged()
    Dim arrCells, c
    Dim sMergeArea As String
    arrCells = Array("JobSpecs", "JobInfo", "JobTest", "EmailAddress")
    For Each c In arrCells
        sMergeArea = Range(c).MergeArea.Address
        Range(c).UnMerge
        Range(c).ClearContents
        Range(sMergeArea).Merge
    Next
End Sub

 

[Rick Rothstein]

You'll need to UnMerge then re-Merge:

It can also be done without UnMerging/ReMerging...

Sub ClearContentsArrayOnMerged()
    Dim RangeName As Variant, NamesArr As Variant, Cell As Range
    NamesArr = Array("JobSpecs", "JobInfo", "JobTest", "EmailAddress")
    Application.ScreenUpdating = False
    For Each RangeName In NamesArr
        For Each Cell In Range(RangeName)
            Cell.MergeArea.ClearContents
        Next
    Next
    Application.ScreenUpdating = True
End Sub

 

[Warship]

but of course! Thx Rick!!
and Simpler still:

Sub ClearContentsArrayOnMerged2()
    Dim arrCells, c
    arrCells = Array("JobSpecs", "JobInfo", "JobTest", "EmailAddress")
    For Each c In arrCells
        Range(c).MergeArea.ClearContents
    Next
End Sub

 

[Rick Rothstein]

but of course! Thx Rick!!
and Simpler still:

Sub ClearContentsArrayOnMerged2()
    Dim arrCells, c
    arrCells = Array("JobSpecs", "JobInfo", "JobTest", "EmailAddress")
    For Each c In arrCells
        Range(c).MergeArea.ClearContents
    Next
End Sub

That code will fail if any of the named ranges have a mixture of merged and non-merged cells in them. Here is a different approach, definitely not simpler, than I used in my last code which should execute much faster...

Sub ClearContentsArrayOnMerged()
    Dim RangeName As Variant, NamesArr As Variant, Cell As Range
    NamesArr = Array("JobSpecs", "JobInfo", "JobTest", "EmailAddress")
    Application.ScreenUpdating = False
    Application.FindFormat.MergeCells = True
    For Each RangeName In NamesArr
        With Range(RangeName)
            If .MergeCells = False Then
                .ClearContents
            Else
                .Replace "*", "", xlWhole, SearchFormat:=True
                .Replace "*", "", xlWhole, SearchFormat:=False
            End If
        End With
    Next
    Application.FindFormat.Clear
    Application.ScreenUpdating = True
End Sub

 

[Warship]

Rick - Now I understand why you had the nested "For Each Cell" loop...