Clear content of cells outside area of interest

[yploo]

Hi all,

I am looking for a way to clear the content of all the cells outside my area of interest (designated by the orange oval):

Close up, it looks like this:

I would like to clear the content of ALL the cells that are not encircled, and including those coloured in orange.

How can this be done?

Thank you very much in advance!!!

 

[Snakehips]

@yploo Do you have two sets of data, ex .TIFF & ex .PNG Images that are on separate sheets in same workbook?
I am assuming that you wish to mirror the contents clearing of the .PNG sheet , in the .TIFF sheet ?

 

[Snakehips]

If so ^^^^ then this goes some way towards doing that. It is just short of clearing the contents in the 'TIFF' sheet of the cells mirroring the 225 cells in the PNG sheet.
@Peter_SSs may provide a slick way to do that if required?

Sub ClearOutside_v3()
  Dim rCol As Range, rFound As Range
  Dim c As Long
  Dim TIFFsht As Worksheet
  
 Set TIFFsht = Sheets("TIFF") '<<<<< Edit Sheet Name
  Application.ScreenUpdating = False
  For Each rCol In ActiveSheet.UsedRange.Columns
    With rCol
      Set rFound = Nothing
        Set rFound = .Find(What:=255, After:=.Cells(.Rows.Count), LookAt:=xlWhole, SearchDirection:=xlNext)
        If rFound Is Nothing Then
        .ClearContents
        TIFFsht.Range(rCol.Address).ClearContents
        Else
          If rFound.row > 1 Then
          Range(.Cells(1), rFound.Offset(-1)).ClearContents
          TIFFsht.Range(Range(.Cells(1), rFound.Offset(-1)).Address).ClearContents
          End If
          Set rFound = .Find(What:=255, After:=.Cells(1), LookAt:=xlWhole, SearchDirection:=xlPrevious)
          If rFound.row < .Cells(.Rows.Count).row Then
          Range(rFound.Offset(1), .Cells(.Rows.Count)).ClearContents
          TIFFsht.Range(Range(rFound.Offset(1), .Cells(.Rows.Count)).Address).ClearContents
          End If
          
        End If
    End With
  Next rCol
  ActiveSheet.UsedRange.Replace What:=255, Replacement:="", LookAt:=xlWhole
  '***  Need  equivalent of 255 cells clearing in TIFF sheet
  
  
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

@yploo Do you have two sets of data, ex .TIFF & ex .PNG Images that are on separate sheets in same workbook?
I am assuming that you wish to mirror the contents clearing of the .PNG sheet , in the .TIFF sheet ?

Ah, Tony, you may well be right. This would be my adaptation of my previous code to just do the clearing on 'TIFF' sheet, based on data on 'PNG' sheet.

Sub ClearOutside_v3()
  Dim PNG As Worksheet, TIFF As Worksheet
  Dim rCol As Range, rFound As Range
  Dim c As Long
  Dim FirstAddr As String
 
  Set PNG = Sheets("PNG")
  Set TIFF = Sheets("TIFF")
  Application.ScreenUpdating = False
  For Each rCol In PNG.UsedRange.Columns
    With rCol
      Set rFound = Nothing
        Set rFound = .Find(What:=255, After:=.Cells(.Rows.Count), LookAt:=xlWhole, SearchDirection:=xlNext)
        If rFound Is Nothing Then
          TIFF.Range(rCol.Address).ClearContents
        Else
          If rFound.Row > 1 Then TIFF.Range(.Cells(1).Address, rFound.Offset(-1).Address).ClearContents
          Set rFound = .Find(What:=255, After:=.Cells(1), LookAt:=xlWhole, SearchDirection:=xlPrevious)
          If rFound.Row < .Cells(.Rows.Count).Row Then TIFF.Range(rFound.Offset(1).Address, .Cells(.Rows.Count).Address).ClearContents
        End If
    End With
  Next rCol
  With PNG.UsedRange
    Set rFound = .Find(What:=255, LookAt:=xlWhole)
    If Not rFound Is Nothing Then
      FirstAddr = rFound.Address
      Do
        TIFF.Range(rFound.Address).ClearContents
        Set rFound = .Find(What:=255, After:=rFound)
      Loop Until rFound.Address = FirstAddr
    End If
  End With
  Application.ScreenUpdating = True
End Sub

 

[Snakehips]

^^^ Looks to be spot on Peter if the assumption is right.

 

[yploo]

Yes it is exactly what I wanted! Thank you very much @Peter_SSs and @Snakehips for your patience and prompt help!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[yploo]

I'm back again with another question... similar to my previous question, but now with 4 circular areas of interest (AOI), instead of 1. Likewise, the boundaries of the AOIs are marked with 255 cells.

My goal is to remove all cell content outside the 4 AOIs, including the 255 cells.

When I used the code previously given by @Snakehips and @Peter_SSs, some of the contents outside the AOIs are still retained, like this:

How can the code be altered to clear ALL the content outside the 4 AOIs?

Thanks a lot in advance!!!

 

[Peter_SSs]

To consider the possibilities, & test, could you provide a sample file? There's an awful lot of manual typing to set that up!
perhaps upload to Dropbox or One Drive or Google Drive etc & provide a shared link here?

 

[yploo]

Ah, I'm terribly sorry to have overlooked that! Here is the WeTransfer link for the file: https://we.tl/t-8ZmzT82VmX

 

[Peter_SSs]

Thanks for the file. I had not looked back at what went before when I asked that, but now that I have, I am also wondering about whether we are again dealing with two sheets, or simply (could be a poor choice of words ) removing all the outsides on this one sheet?

Can I assume that the 4 AOIs are all the same size?

Can I assume that the 4 AOIs are neatly aligned with each other vertically and horizontally like the sample?