PDA

View Full Version : ClearingContents in ranges that don't intersect



JoeSal
01-03-2020, 09:04 PM
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
01-04-2020, 01:25 AM
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
01-04-2020, 07:04 AM
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
01-04-2020, 10:29 AM
Thanks! It worked, but very slow (I'll try to get the 25,000 cells down to a smaller range).

snb
01-04-2020, 10:31 AM
I conclude that you didn't test my suggestion.

JoeSal
01-04-2020, 10:32 AM
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
01-04-2020, 10:49 AM
You should of course adapt the range "A1:F13" first

JoeSal
01-04-2020, 10:56 AM
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
01-04-2020, 03:37 PM
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
01-04-2020, 07:48 PM
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
01-05-2020, 03:26 AM
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