There's nothing built into excel that'll do this range subtraction.

But Tom Ogilvy posted a neat idea that uses a temporary worksheet....

Option Explicit
Sub testme()
Dim myNames As Variant
Dim tempWks As Worksheet
Dim iCtr As Long

Set tempWks = Worksheets.Add

myNames = Array("name1", "name2", "name3") 'add all 14

With Worksheets("sheet1")
tempWks.Range(.UsedRange.Address) = 1
For iCtr = LBound(myNames) To UBound(myNames)
tempWks.Range(.Range(myNames(iCtr)).Address).Clear Contents
Next iCtr

On Error Resume Next
.Range(tempWks.Cells.SpecialCells(xlCellTypeConsta nts).Address) _
.ClearContents
On Error GoTo 0
End With

Application.DisplayAlerts = False
tempWks.Delete
Application.DisplayAlerts = True


End Sub


A few people have tried to make it not use the extra worksheet.

In your situation, they would have applied data|validation to all the cells.
cleared the data|validation from the named ranges, then cleared the contents of
all the cells that still had DV. (then cleaned up the DV).

I personally don't like this--if you use DV in any of your cells, then you have
to find another way. I like Tom's suggestion--it'll work.



Mike Fogleman wrote:

Dave, thanks for your effort on this and it works so far without a hitch
through several import cycles. However, I am not scraping my original method
just yet, which is to move the named ranges to another sheet while I clear
sheet1 and then put the ranges back where they were. Can this be done
without looping (speed issue) ? I was trying to use range objects against
each other like
UsedRange-MyBigNamedRange=WhatsLeftUnamedRange.
WhatsLeftUnamedRange.ClearContents
So far, no luck.
Mike F
"Dave Peterson" wrote in message
...
You want ALL the cells that aren't part of those 14 named ranges
cleared???

If yes, then this worked ok for me:

Option Explicit
Sub testme()
Dim myNames As Variant
Dim myCell As Range
Dim iCtr As Long
Dim myBigRng As Range

myNames = Array("name1", "name2", "name3") 'add all 14
With Worksheets("sheet1")

Set myBigRng = .Range(myNames(LBound(myNames)))
For iCtr = LBound(myNames) + 1 To UBound(myNames)
Set myBigRng = Union(myBigRng, .Range(myNames(iCtr)))
Next iCtr

For Each myCell In .UsedRange.Cells
If IsEmpty(myCell) Then
'do nothing
Else
If Intersect(myBigRng, myCell) Is Nothing Then
myCell.ClearContents
End If
End If
Next myCell

End With

End Sub

It builds a giant range based on the 14 (or whatever) names you give it.
Then
it just cycles through all of the cells in the usedrange of that sheet.




Mike Fogleman wrote:

On Sheet1 I have 14 named ranges. Data is imported and placed around
these
ranges so formulas within the ranges do calculations on the imported
data.
The named ranges will resize according to the amount of data imported,
within certain known limits. I would like to be able to "reset" my sheet
by
clearing all cells that are not within a named range, so I can calculate
the
next imported dataset. Is this doable?
Mike F

--

Dave Peterson


--

Dave Peterson