Clear content of cells outside area of interest

yploo

New Member
Joined
Aug 2, 2021
Messages
19
Office Version
  1. 2013
Platform
  1. Windows
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):
Left.png
Right.png


Close up, it looks like this:
Zoomed.png


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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
@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?
 
Upvote 0
@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.
 
Upvote 0
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.

VBA 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.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?
 
Upvote 0
@Peter_SSs @yploo is also looking to clear the border cells.
So maybe the following supplement to your code?

VBA 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
 
Upvote 0
@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:

Rich (BB 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
 
Upvote 0
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:
PNG.png


And this is data extracted from .TIFF pic:
TIFF.png


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!!!
 
Upvote 0
I'm afraid that I don't understand and I cannot see the significance of the two images.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top