clearing empty (but possibly formatted) cells

[Brian.Crawford]

I have an working range which I can define (at any particular point) and wish to CLEAR the rest of the WS of any possible formatting, borders or other leftover content. Is there a function to exclude my working range from the entire WS or what excel thinks is the "UsedRange" (vs the real UsedRange which I'm trying to get to). Is ther something like the inverse of a "Union" or "non-Intersect" that can define everything but my actual range (other than setting a range with specific paramters (like max row & col numbers).

This is "sort of" the usual reset UsedRange problem for which there seems to be hundreds of less than satisfactory or conditional solutions out there.

Thanks all
Brian

 

[wigi]

Hello Brian,

Using Google to find (slightly adapted for the UsedRange):

Sub Test()
    NotIntersect(ActiveSheet.UsedRange, Application.InputBox("", , , , , , , 8)).Select
End Sub


Function NotIntersect(rng As Range, x As Range) As Range
' copyright 2001-2004 Jim Wilcox
    Dim y As Range
    On Error Resume Next
    If rng.Parent Is x.Parent Then
        With x
            Set y = myUnion(y, Range(Rows(1), .Rows(0)))
            Set y = myUnion(y, Range(Rows(Rows.Count), .Rows(.Rows.Count + 1)))
            Set y = Intersect(y, .EntireColumn)
            Set y = myUnion(y, Range(Columns(1), .Columns(0)))
            Set y = myUnion(y, Range(Columns(Columns.Count), .Columns(.Columns.Count + 1)))
            Set y = Intersect(y, rng)
        End With
        Set NotIntersect = y
    End If
    On Error GoTo 0
End Function


Private Function myUnion(o As Range, rng As Range) As Range
    On Error Resume Next
    If o Is Nothing Then
        Set myUnion = rng
    ElseIf rng Is Nothing Then
        Set myUnion = o
    Else
        Set myUnion = Union(o, rng)
    End If
    On Error GoTo 0
End Function

 

[Brian.Crawford]

I think this will take a little reviewing for me to fully understand this although I get what its intendeing to do
thanks very much...
Brian

 

[Rick Rothstein]

Here is a completely different approach that you might be able to use. It is in the form of a subroutine that you call from within your own code passing it the range you want to leave as is. In the example below, I use Selection as the range to leave alone but any valid Excel range should work as well.

' An example of "your own code" calling the ClearAllExcept subroutine
Sub Test()
  ClearAllExcept Selection
End Sub

' Your own code calls this subroutine
Sub ClearAllExcept(Rng As Range)
  Dim OrigAddr As String, OrigUsedRangeAddr As String, AllButAddr As String, WS As Worksheet
  OrigAddr = Rng.Address
  OrigUsedRangeAddr = ActiveSheet.UsedRange.Address
  Application.ScreenUpdating = False
  Set WS = Worksheets.Add
  WS.Range(OrigUsedRangeAddr).Value = "X"
  WS.Range(OrigAddr).Clear
  AllButAddr = WS.Cells.SpecialCells(xlConstants).Address
  Application.DisplayAlerts = False
  WS.Delete
  Application.DisplayAlerts = True
  Range(AllButAddr).Clear
  Application.ScreenUpdating = True
End Sub

 

[wigi]

I think this will take a little reviewing for me to fully understand this although I get what its intendeing to do
thanks very much...
Brian

Hello Brian

Should you have questions about the code, please feel free to ask.