ClearContents - Except NamedRanges

M

Mike Fogleman

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
 
G

Guest

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
 
D

Dave Peterson

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.
 
N

Norman Jones

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
 
D

Dave Peterson

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.
 
D

Dave Peterson

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

Norman Jones

Hi Dave,
I think that this breaks for multiple area ranges in xl97 (or xl2k).
Click to expand...

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.
 
M

Mike Fogleman

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
 
D

Dave Peterson

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).ClearContents
Next iCtr

On Error Resume Next
.Range(tempWks.Cells.SpecialCells(xlCellTypeConstants).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.
 
Top