Consulting

Results 1 to 11 of 11

Thread: ClearingContents in ranges that don't intersect

  1. #1
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    5
    Location

    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

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    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

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    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

  4. #4
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    5
    Location
    Thanks! It worked, but very slow (I'll try to get the 25,000 cells down to a smaller range).

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    I conclude that you didn't test my suggestion.

  6. #6
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    5
    Location
    Quote Originally Posted by snb View Post
    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?

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    You should of course adapt the range "A1:F13" first

  8. #8
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    5
    Location
    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

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    Please, use code tags !!

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

  10. #10
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    5
    Location
    Quote Originally Posted by snb View Post
    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!

  11. #11
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •