Named ranges disappearing in Excel 2007

M

Mike

I have some named ranges (no more than 15) in a workbook and I am assigning
their values to string variables. For some reason my named ranges
are randomly disappearing after my code runs.

The syntax in VBA looks like this:

strDate = Worksheets("SheetName").Range("NamedRange1").Value

Any ideas?????
 
B

Bill Renaud

Are you deleting the cells of the named range somewhere in your code? The
named range will refer to 'Worksheet Name'!#REF! after the delete, and will
be invalid the next time you try to use it.

You will have to step through all of your code to figure out where this is
happening.

You might consider including an error hander and doing it like this:

Public Sub Test()
Dim rngNamedRange As Range

On Error Resume Next

Set rngNamedRange = ThisWorkbook.Names("NamedRange").RefersToRange

If rngNamedRange Is Nothing _
Then
MsgBox "'NamedRange' has disappeared!", vbCritical + vbOKOnly
Else
'Continue processing.
End If
End Sub
 
D

David J Richardson

Mike said:
I have some named ranges (no more than 15) in a workbook and I am
assigning their values to string variables. For some reason my named
ranges are randomly disappearing after my code runs.

I have seen this happen recently, but only once. I don't know whether
the disappearance was related to code running or not.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top