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!!!

 

[Peter_SSs]

Thanks for that overview. Even though much of it is well over my head, it is interesting reading and at least I now have some concept of what I was dealing with.
Again I would acknowledge @Snakehips who was able to understand what you were trying to achieve in Excel terms & express that in a way that I could understand.
I am pleased that we were able to assist and wish you good luck with your studies!

 

[yploo]

Hello @Peter_SSs, I am back again with another issue ?

Previously, your code helped to clear all the cell content outside the 4 area of interests (AOI), the boundaries of which are defined by cells with the value of 255. The samples were placed next to each other, like this:

However, the experiment results had been undesirable, and I wanted to repeat the experiment with a different placement of the samples (ie. place them further apart from each other):

Somehow, the code that you wrote works for the first set of placement, where the 4 AOIs are close to each other, but not the second set.

The issue is always with this line of the code:

Would you be able to help me again? I regret that I don't have sufficient knowledge in VBA to amend the code ?

The Excel workbook containing both the PNG and TIFF worksheets can be found here: https://wetransfer.com/downloads/3870a3991cd19d18ff2ed442708a61ce20220126091123/6ada36bc4f3671bd5f07346d7966e10f20220126091152/1700d1

All the 255 cells are coloured in green in the PNG sheet.

Thank you very much in advance!!!

 

[Peter_SSs]

This project is pretty hazy to me and adjusting that code for the more unaligned circles was proving difficult so I have taken a new approach. Simpler anyway - providing it works. ?

Give this single procedure a try.

Sub ClearOutside_v7()
  Dim wsTIFF As Worksheet
  Dim rA As Range, rBlank As Range, rErr As Range
  Dim c As Long, cols As Long
  
  Application.ScreenUpdating = False
  Set wsTIFF = Sheets("TIFF")
  wsTIFF.Columns(1).Insert
  With Sheets("PNG")
    .Columns(1).Insert
    cols = .Cells(1, Columns.Count).End(xlToLeft).Column
    .UsedRange.Replace What:=255, Replacement:="#N/A", LookAt:=xlWhole
    For c = 2 To cols
      For Each rA In .Columns(c).SpecialCells(2, 3).Areas
        Set rBlank = Nothing
        On Error Resume Next
        Set rBlank = rA.Offset(, -1).SpecialCells(4)
        On Error GoTo 0
        If Not rBlank Is Nothing Then
          rA.ClearContents
          wsTIFF.Range(rA.Address).ClearContents
        End If
      Next rA
    Next c
    For c = 2 To cols
      Set rErr = Nothing
      On Error Resume Next
      Set rErr = .Columns(c).SpecialCells(2, 16)
      On Error GoTo 0
      If Not rErr Is Nothing Then
        For Each rA In rErr.Areas
          rA.ClearContents
          wsTIFF.Range(rA.Address).ClearContents
        Next rA
      End If
    Next c
    .Columns(1).Delete
  End With
  wsTIFF.Columns(1).Delete
  Application.ScreenUpdating = True
  MsgBox "Done"
End Sub

 

[yploo]

Thank you so much @Peter_SSs, the code is perfect!!!

 

[Peter_SSs]

Cheers. Glad it seems to be working for you.