+ Reply to Thread
Results 1 to 6 of 6

How to select and delete a range of cells between two different ranges of unequal size?

Hybrid View

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    91

    How to select and delete a range of cells between two different ranges of unequal size?

    Hello,

    I need to be able to select all the cells in between all the $$ (Range B3:G6). The goal is to delete them leaving any columns to the right/left or rows above/below alone.

    I am currently using a "union" to select all the $$ but the range inbetween them is not selected. The range between the $$ is dynamic from one worksheet to another.

    Any help would be great.

    Here is an example of the table:
    # AAA BBB CCC DDD EEE FFF AAA BBB CCC
    Time A B C D E F A B C
    0 $$ $$ $$ $$ $$ $$ 27 64 88
    0.1 7 7 18.12 0.1503 29.83 42 503 879.2 200
    0.2 7 7 18.12 0.1503 29.83 42 503 879.2 200
    0.3 $$ 7 18.12 0.1503 29.83 42 503 879.2 200


    Sub Select_Range()
    Dim FindRng As Range
        Dim c As Range
        Dim First As String
        Dim rng As Range
        Set FindRng = ActiveSheet.UsedRange
        With FindRng
            Set c = .Find(What:="$$", After:=FindRng.Cells(1, 1), LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not c Is Nothing Then
                First = c.Address
                On Error Resume Next
                Set rng = c
                Do
                    Set rng = Union(rng, c)
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> First
            End If
            rng.Select
        End With
    End Sub

  2. #2
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

    Re: How to select and delete a range of cells between two different ranges of unequal size

    Not sure if this helps but try using the intersect function to test if a cell is within a range.

    if intersect(range1, range2) is nothing then
    
    'if it does not intersect
    
    else
    
    'if it does intersect
    
    end if

  3. #3
    Registered User
    Join Date
    04-25-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: How to select and delete a range of cells between two different ranges of unequal size

    Hello GaidenFocus,

    Range1 is dynamic from worksheet to worksheet and the ranges will never intersect. My issue is selecting the range in between.

    Thanks for the suggestion though

  4. #4
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

    Re: How to select and delete a range of cells between two different ranges of unequal size

    You could make the whole thing your range, and if it intersects your dynamic range, delete, or don't delete based on what that range is.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: How to select and delete a range of cells between two different ranges of unequal size

    Perhaps
    With rng
        Set compactRng = Range(.Item(1), .Item(.cells.count))
    End With
    
    compactRng.ClearContents
    Or you could use the two argument form of Range to collect your found cells, instead of Union.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Registered User
    Join Date
    04-25-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: How to select and delete a range of cells between two different ranges of unequal size

    Mikerickson,

    Thanks for the great idea! Works perfect!

    See below for the fix.

    Sub Select_Range()
    Dim FindRng As Range
        Dim c As Range
        Dim First As String
        Dim rng As Range
        Set FindRng = ActiveSheet.UsedRange
        With FindRng
            Set c = .Find(What:="$$", After:=FindRng.Cells(1, 1), LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not c Is Nothing Then
                First = c.Address
                On Error Resume Next
                Set rng = c
                Do
                    Set rng = Range(rng, c)
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> First
            End If
            rng.Select
        End With
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1