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

 

[yploo]

It works perfect! Thank you so much for your help!!!

 

[Peter_SSs]

It works perfect! Thank you so much for your help!!!

Cheers. Glad we got there in the end. Thanks for the confirmation.

 

[yploo]

Sorry @Peter_SSs, I just noticed that the clearing of content is not 100% mirrored on the TIFF sheet (both sheets already have Column A deleted). An example is as follows: NT151 is blank on PNG sheet, but it has content on TIFF sheet.

Sorry I didn't notice this earlier...

 

[Peter_SSs]

Can you provide a link to a copy of a workbook that has those PNG & TIFF sheets before the code was run?

 

[yploo]

Here is the link to the workbook you requested: https://we.tl/t-l9fnmhD5Su

 

[Peter_SSs]

Thanks for the workbook.
Oops, somewhere along the line I forgot that the TIFF sheet does not have the same 255 cells as the PNG sheet.

Try with ClearOutside_v5 from post #30, but replace the previous ClearCircle code with this one.

Disclaimer: I have not checked the results extremely closely. The number of cells is enormous and when I zoom down to a manageable overall size, my eyes go crazy trying to see the individual cells and what is in them and where they are! ?

Sub ClearCircle(rng As Range)
  Dim PNG As Worksheet, TIFF As Worksheet
  Dim rCol As Range, rngFound As Range, rNA As Range
  
  Set PNG = Sheets("PNG")
  Set TIFF = Sheets("TIFF")
  For Each rCol In rng.Columns
    With rCol
      Set rngFound = Nothing
        Set rngFound = .Find(What:=255, LookIn:=xlFormulas, After:=.Cells(.Rows.Count), LookAt:=xlWhole, SearchDirection:=xlNext)
        If rngFound Is Nothing Then
          rCol.ClearContents
          TIFF.Range(rCol.Address).ClearContents
        Else
          If rngFound.Row > .Row Then
            .Cells(1).Resize(rngFound.Offset(-1).Row - .Row + 1).ClearContents
            TIFF.Range(.Cells(1).Address, rngFound.Offset(-1).Address).ClearContents
          End If
          Set rngFound = .Find(What:=255, LookIn:=xlFormulas, After:=.Cells(1), LookAt:=xlWhole, SearchDirection:=xlPrevious)
          If rngFound.Row < .Cells(.Rows.Count).Row Then
            rngFound.Offset(1).Resize(.Row + .Rows.Count - rngFound.Row - 1).ClearContents
            TIFF.Range(rngFound.Offset(1).Address, .Cells(.Rows.Count).Address).ClearContents
          End If
          .Replace What:=255, Replacement:="#N/A", LookAt:=xlWhole
          Set rNA = .SpecialCells(xlConstants, xlErrors)
          rNA.ClearContents
          TIFF.Range(rNA.Address).ClearContents
        End If
    End With
  Next rCol
End Sub

 

[yploo]

Yes, I definitely should have checked more closely ? . Nonetheless, this set of code works perfectly, and I appreciate your help very much!!!

 

[Peter_SSs]

You're welcome. It was an interesting exercise.

BTW, a very minor thing but the ClearCircle procedure has a little unused code that I never got around to tidying up. These bits could be removed as this procedure does not specifically refer to the PNG sheet.
That reference is implicitly included as part of the 'rng' argument that is passed to the procedure.

Sub ClearCircle(rng As Range)
  Dim PNG As Worksheet, TIFF As Worksheet
  Dim rCol As Range, rngFound As Range, rNA As Range
  
  Set PNG = Sheets("PNG")
  Set TIFF = Sheets("TIFF")
  For Each rCol In rng.Columns

 

[Peter_SSs]

It was an interesting exercise.

BTW, is it possible in just a couple of sentences to give an idea about what this is about? It has intrigued me.

 

[yploo]

It's a pleasant surprise you're interested in knowing about my work! Sure, I will try to explain it here.

I am studying microalgae that live on or near the surface of marine sediment; these organisms are also known as microphytobenthos (MPB). I want to compare the photosynthetic capacities of the MPB in mangrove and tidal flat habitats. Many studies have been performed on tidal flat MPB but none on mangrove MPB. The mangrove MPB are often overlooked because of the belief that the mangrove canopy shades off a large amount of light, therefore the presence and contribution of its MPB to the habitat is insignificant.

I am using a fluorescence method to identify and measure MPB photosynthetic capacity in sediment samples. The very large amount of data in the Excel sheets are 8-bit greyscale values generated by the fluorescence machine. At the very beginning I had a sediment sample contained in a big dish, which practically took up more than half the area of detection, so there could only be one AOI per measurement. Later, I found dishes small enough such that I am able to measure 4 samples simultaneously, hence the 4 AOIs.

By the way, our preliminary data suggests that mangrove MPB could be even more productive than the tidal flat MPB, despite the generally lower amount of light they receive!

I am new to VBA and I wouldn't have been able to achieve this finding without your help! So thank you!!!