ClearContents - Except NamedRanges
 

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

ClearContents - Except NamedRanges
 

Hi Mike;

There are several ways to get at it but this might be the
simplest. I understood you want to get rid of anything
that does not have a formula. You can use something like
this:

Sub try()

Range("A1").Select
Selection.CurrentRegion.Select
For Each C In Selection

If Not C.HasFormula = True Then
C.ClearContents

End If

Next

End Sub


It grabs the current region parses each cell and if it
does not have a formula then clears out the contents.

Thanks,

Greg

-----Original Message-----
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


.

ClearContents - Except NamedRanges
 

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

ClearContents - Except NamedRanges
 

Hi Mike,

Another approach:

Sub Tester1()
Dim Arr As Variant
Dim Rng As Range

Set Rng = Union(Range("One"), Range("Two")) 'Extend to 14 ranges
Arr = Rng.Formula
ActiveSheet.UsedRange.ClearContents
Rng = Arr

End Sub


---
Regards,
Norman



"Mike Fogleman" wrote in message
news:8NZYc.1473$3l3.919@attbi_s03...
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

ClearContents - Except NamedRanges
 

I think that this breaks for multiple area ranges in xl97 (or xl2k).

It didn't work for me in xl2002 with multiple areas in Rng.



Norman Jones wrote:

Hi Mike,

Another approach:

Sub Tester1()
Dim Arr As Variant
Dim Rng As Range

Set Rng = Union(Range("One"), Range("Two")) 'Extend to 14 ranges
Arr = Rng.Formula
ActiveSheet.UsedRange.ClearContents
Rng = Arr

End Sub

---
Regards,
Norman

"Mike Fogleman" wrote in message
news:8NZYc.1473$3l3.919@attbi_s03...
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

ClearContents - Except NamedRanges
 

But you could set up 14 ranges (or an array of ranges) and do them individually.



Dave Peterson wrote:

I think that this breaks for multiple area ranges in xl97 (or xl2k).

It didn't work for me in xl2002 with multiple areas in Rng.

Norman Jones wrote:

Hi Mike,

Another approach:

Sub Tester1()
Dim Arr As Variant
Dim Rng As Range

Set Rng = Union(Range("One"), Range("Two")) 'Extend to 14 ranges
Arr = Rng.Formula
ActiveSheet.UsedRange.ClearContents
Rng = Arr

End Sub

---
Regards,
Norman

"Mike Fogleman" wrote in message
news:8NZYc.1473$3l3.919@attbi_s03...
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

ClearContents - Except NamedRanges
 

Hi Dave,

I think that this breaks for multiple area ranges in xl97 (or xl2k).

Indeed it does and how! But the pattern of breakage is at once interesting
and frustratingly elusive.

I did test prior to posting but, regrettably, my test data suffered from
fatal flaws. Being concerned with numbers like 5461 and 8192 and the number
of discrete ranges, I populated my test ranges with a common formula (=Row).
This successfully hid one problem. Secondly, my first range was larger than
the others and this hid another (superable) problem.

I guess that with such flawed methadology, the results were only too
predictable - GIGO!

Thank you for your correction.

---
Regards,
Norman



"Dave Peterson" wrote in message
...
I think that this breaks for multiple area ranges in xl97 (or xl2k).

It didn't work for me in xl2002 with multiple areas in Rng.



Norman Jones wrote:

Hi Mike,

Another approach:

Sub Tester1()
Dim Arr As Variant
Dim Rng As Range

Set Rng = Union(Range("One"), Range("Two")) 'Extend to 14 ranges
Arr = Rng.Formula
ActiveSheet.UsedRange.ClearContents
Rng = Arr

End Sub

---
Regards,
Norman

"Mike Fogleman" wrote in message
news:8NZYc.1473$3l3.919@attbi_s03...
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

ClearContents - Except NamedRanges
 

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

ClearContents - Except NamedRanges
 

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