1
votes

I have a named range in Excel that I am trying to clear using VBA.

The problem with the range is that spans across multiple sheets and I am not sure how to properly reference the named range in VBA since it covers multiple sheets.

The Named Range "Counts" is set to Workbook scope with the following cell references:

=Sheet1!$A$1, Sheet2!$A$1, Sheet3!$A$1

When clearing a named range where it only has cells referenced on one sheet I use the following:

ThisWorkbook.Sheets("Sheet1").Range("Counts").ClearContents

I have tried the following but neither seemed to work.

ThisWorkbook.Range("Counts").ClearContents

and

Range("Counts").ClearContents

The last gives me a global error.

3
How did you define the range name across multiple sheets? Can you post the range name formula? - teylyn
I have added this now to the original question - Matt
Microsoft says to do this but it also does not work Sub ClearRange() Application.Goto Reference:="MyRange" Selection.ClearContents End Sub - Matt
How are you using the range? I cannot find anything that works with such a range, not even SUM(). So what are you doing with it? - teylyn
I was just wanting it to clear the named range areas if possible without having multiple named ranges per sheet to avoid confusion but it appears this is not possible. - Matt

3 Answers

2
votes

Instead of a range that goes across multiple sheets (which does not work, as we have established), you need a worksheet scoped range in each sheet.

When defining a range name you can set its scope to workbook or the current sheet. This way you can have the same range name in many sheets.

Use VBA to loop through all worksheet, access the ws.Range("TheRangeName") on the current sheet and clear its contents.

That's a cleaner approach.

1
votes

I would write something that displays the names and you can use that to remove it...

In the immediate window:

For i = 1 to names.count:Debug.print i, Names(i).RefersTo, Names(i).name:next

You can then either use th name or the index to remove the particular name you want.

Hope that helps.

0
votes

Rory Archibald is a Microsoft MVP

On his website post about named ranges he states that: A Range object can only refer to cells on one worksheet. You cannot refer to cells on different sheets with one Range object.

So I am unable to accomplish what I was wanting but I should be able to just create multiple named ranges and just clear them one at a time.

I was able to complete the code for this as follows with inspiration from teylyn

Public Sub ClearRanges()
'Checks if named Range exists on sheet if it does then clear contents

    Dim ws As Worksheet

    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Worksheets
        If Len(ws.Range("Counts").Name) <> 0 Then
            ws.Range("Counts").ClearContents
        End If
    Next ws

    Application.DisplayAlerts = True
End Sub