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 You will be needing to utilise custom vba.
It may help get a solution if you can answer the below.
Do, as your close up might suggest, all the border cells all contain the same value that is unique to the border?
Can you give an indication of the scale of the data range?

 

[yploo]

@yploo You will be needing to utilise custom vba.
It may help get a solution if you can answer the below.
Do, as your close up might suggest, all the border cells all contain the same value that is unique to the border?
Can you give an indication of the scale of the data range?

"Do all the border cells contain the same value that is unique to the border?"
Yes, all the border cells are uniquely 255.

Not taking the border into consideration, the data value ranges from 0 to 245.

 

[Peter_SSs]

Yes, all the border cells are uniquely 255.

Not taking the border into consideration, the data value ranges from 0 to 245.

Give this a try with a copy of your workbook.

Sub ClearOutside()
  Dim rCol As Range, rFound As Range
  Dim c As Long
 
  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
        Else
          If rFound.Row > 1 Then Range(.Cells(1), rFound.Offset(-1)).ClearContents
          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
        End If
    End With
  Next rCol
  Application.ScreenUpdating = True
End Sub

BTW, why not include your Excel version(s) in your profile as that can sometimes make a difference to the solution method?

 

[Snakehips]

@Peter_SSs @yploo is also looking to clear the border cells.
So maybe the following supplement to your code?

Sub ClearOutside()

  Dim rCol As Range, rFound As Range
  Dim c As Long
 
  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
        Else
        
          If rFound.row > 1 Then Range(.Cells(1), rFound).ClearContents
          Set rFound = .Find(What:=255, After:=.Cells(1), LookAt:=xlWhole, SearchDirection:=xlPrevious)
          If rFound.row < .Cells(.Rows.Count).row Then Range(rFound, .Cells(.Rows.Count)).ClearContents
          
          Do Until rFound Is Nothing
            Set rFound = .Find(What:=255, After:=.Cells(1), LookAt:=xlWhole, SearchDirection:=xlPrevious)
            If Not rFound Is Nothing Then rFound.ClearContents
          Loop
                  
        End If
    End With
  Next rCol
   
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

@Peter_SSs @yploo is also looking to clear the border cells.
So maybe the following supplement to your code?

I wasn't sure about the "255" cells but with my sample data, your code produced an error.
If the 255 cells are to be removed too then my modification would be the addition of this one extra line of code:

Sub ClearOutside_v2()
  Dim rCol As Range, rFound As Range
  Dim c As Long
  
  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
        Else
          If rFound.Row > 1 Then Range(.Cells(1), rFound.Offset(-1)).ClearContents
          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
        End If
    End With
  Next rCol
  ActiveSheet.UsedRange.Replace What:=255, Replacement:="", LookAt:=xlWhole
  Application.ScreenUpdating = True
End Sub

 

[yploo]

Thank you @Peter_SSs and @Snakehips for your prompt help!!!

Is there a way to find out the exact location of each 255 cell?

 

[Peter_SSs]

Is there a way to find out the exact location of each 255 cell?

Can you explain exactly what you mean by that and how/where it would be reported?

 

[yploo]

Sorry for late reply, I needed some time to think about the workflow again. Please allow me to explain the thought process from the beginning:

I have an area of interest (AOI) among the worksheet of data, and the AOI is defined by a circular pattern of the value 255. My intention was to identify all the cells (and their coordinates) enclosed within the 255 cells.

But apparently, my equipment generates the pic with a conspicuous AOI defined by 255 only in .PNG format, but I need pics in .TIFF format for data analysis. My intention now is to narrow down the scope of view to the cells enclosed by the AOI in the worksheet of data extracted from .TIFF pics. Again, data from .TIFF pics means there is no indication of border with the value of 255.

For comparison, here is data extracted from .PNG pic:

And this is data extracted from .TIFF pic:

So, what I wish to achieve now is to erase all the cells outside the AOI in the .TIFF data, using the information of AOI border coordinates from the .PNG data. Is it possible to do so? If so, which formula or code should I look into?

Thank you very much in advance for your time!!!

 

[Peter_SSs]

I'm afraid that I don't understand and I cannot see the significance of the two images.