ClearingContents in ranges that don't intersect

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

Hi JoeSal!
Welcome to vbax forum!
Maybe:

Code:

---

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

---

Or (reducing worksheet interaction):

Code:

---

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 worked, but very slow (I'll try to get the 25,000 cells down to a smaller range).

I conclude that you didn't test my suggestion.

Quote:

---

Originally Posted by snb

Or (reducing worksheet interaction):

Code:

---

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?

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

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

Please, use code tags !!

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

Quote:

---

Originally Posted by 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.

---

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

Logic is the bais of programming.

To avoid error messages:

Code:

---

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

---