ClearingContents in ranges that don't intersect

[JoeSal]

Hi, I'd like to clear contents in cells that do not intersect with a range. Something along the lines of (or anything faster):

For each cell in sheets("responders").range("B111:B25000")
if "cell range" <> sheets("responders").range(var) then
"cell range".clearcontents
end if
next cell


"cell range" - I'm not sure how to refer to the range of each cell in the loop, e.g. B111, B112, B113, etc.
var - this is a string I've calculated elsewhere that refers to a range within ("B111:B25000").

In other words, within range("B111:B25000") I'd like to clear contents in any cells, B111, B112, B113,....B25000, that don't intersect with range(var).

I've come across the application.intersect method, but not sure if that would be better?

The alternative perhaps would be to only populate range(var) to begin with , but it takes too long to run.

Thanks

[大灰狼1976]

Hi JoeSal!
Welcome to vbax forum!
Maybe:

For each cell in sheets("responders").range("B111:B25000").cells
    if intersect(cell , sheets("responders").range(var)) is nothing then
        cell.clearcontents
    end if
next cell

[snb]

Or (reducing worksheet interaction):

Sub M_snb_001()
   For Each it In Range("A1:F13")
      If Intersect(it, Range("B111:B25000")) Is Nothing Then
         If IsEmpty(b_01) Then Set b_01 = it
         Set b_01 = Application.Union(b_01, it)
      End If
   Next
   
   b_01.ClearContents
End Sub

[JoeSal]

Thanks! It worked, but very slow (I'll try to get the 25,000 cells down to a smaller range).

[snb]

I conclude that you didn't test my suggestion.

[JoeSal]

Or (reducing worksheet interaction):

Sub M_snb_001()
   For Each it In Range("A1:F13")
      If Intersect(it, Range("B111:B25000")) Is Nothing Then
         If IsEmpty(b_01) Then Set b_01 = it
         Set b_01 = Application.Union(b_01, it)
      End If
   Next
   
   b_01.ClearContents
End Sub

Thanks. It's saying that object required for "b_01.ClearContents". Do I need to define that elsewhere?

[snb]

You should of course adapt the range "A1:F13" first

[JoeSal]

Thanks. I had changed that to the range I'm using in my workbook, which is Sheets("percent").Range(trial_CR). Here is the code:.

For trial_row = 8 To 107


trial_st = col2_st + CStr(trial_row)
trial_end = col2_end + CStr(trial_row)
trial_st_v2 = 109 + Sheets("input").Range(trial_st).Value
trial_end_v2 = 109 + Sheets("input").Range(trial_end).Value
If trial_end_v2 = 109 Then Exit For
trial_CR = "B" + CStr(trial_st_v2) + ":" + "B" + CStr(trial_end_v2)


Sheets("percent").Range(trial_CR) = Sheets("input").Range("F" + CStr(trial_row)).Value



'For Each cell In Sheets("percent").Range("B111:B25000").Cells
'If Intersect(cell, Sheets("percent").Range(trial_CR)) Is Nothing Then
'cell.ClearContents
'End If
'Next cell



For Each it In Sheets("percent").Range(trial_CR)
If Intersect(it, Sheets("percent").Range("B111:B25000")) Is Nothing Then
If IsEmpty(b_01) Then Set b_01 = it
Set b_01 = Application.Union(b_01, it)
End If
Next


b_01.ClearContents
'
'
Next trial_row

[snb]

Please, use code tags !!

If Range 'trial_CR' doesn't contain any cell that lies outside Range("B111:B25000") b_01 doesn't exist.

[JoeSal]

Please, use code tags !!

If Range 'trial_CR' doesn't contain any cell that lies outside Range("B111:B25000") b_01 doesn't exist.

Yeah, it doesn't exist outside of the range so that makes sense. Thanks!

[snb]

Logic is the bais of programming.

To avoid error messages:

Sub M_snb_001()
   For Each it In Range("A1:F13")
      If Intersect(it, Range("B111:B25000")) Is Nothing Then
         If IsEmpty(b_01) Then Set b_01 = it
         Set b_01 = Application.Union(b_01, it)
      End If
   Next
   
   if not isempty(b_01) then b_01.ClearContents
End Sub