Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting, pastevalues, clearcontents | Excel Discussion (Misc queries) | |||
ClearContents Not Clearing Query | Excel Programming | |||
Clearcontents | Excel Programming | |||
Clearcontents | Excel Programming | |||
Error of ClearContents | Excel Programming |