exclude a range from another range

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
hello
i try to exclude one range from another range.
Code:
Sub ddt()
Dim fd As Range, a As Range
Set a = Range(Cells(1, 1), Cells(20, 10))
Set fd = Cells(7, 5)
Set a = a - fd
a.Value = 1
End Sub
i want to exclude range fd from range a. any solution?
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Code:
Sub selectme()
Dim a As Range
Dim fd As Range
Dim newRange As Range
Set a = Range(Cells(1, 1), Cells(20, 10))
Set fd = Cells(7, 5)
Intersect(a, fd).Value = "Hi"
Set newRange = Union(fd, a).SpecialCells(xlCellTypeBlanks)
Intersect(a, fd).Value = ""
newRange.Select
End Sub
 

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
Code:
Sub selectme()
Dim a As Range
Dim fd As Range
Dim newRange As Range
Set a = Range(Cells(1, 1), Cells(20, 10))
Set fd = Cells(7, 5)
Intersect(a, fd).Value = "Hi"
Set newRange = Union(fd, a).SpecialCells(xlCellTypeBlanks)
Intersect(a, fd).Value = ""
newRange.Select
End Sub
this does not work. the intersect code is not suitable for the case of excluding range
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
This should also do the same as the above code, however, if there is existing data in fd it won't be overwritten.
Also, I'm not sure VBAGeek's code will work correctly if there is already data in Range a

Code:
Sub ddt()
Dim fd As Range, a As Range
    Set a = Range(Cells(1, 1), Cells(20, 10))
    Set fd = Cells(7, 5)
    
    Set RngExc = Nothing
    For Each Cll In a
        If Application.Intersect(Cll, fd) Is Nothing Then
            If RngExc Is Nothing Then
                Set RngExc = Cll
            Else
                Set RngExc = Application.Union(RngExc, Cll)
            End If
        End If
    Next Cll
    RngExc.Value = 1
End Sub
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG18Jun14
[COLOR="Navy"]Dim[/COLOR] fd [COLOR="Navy"]As[/COLOR] Range, a [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range(Cells(1, 1), Cells(20, 10))
 [COLOR="Navy"]If[/COLOR] Not R.Address = Cells(7, 5).Address [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] a [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] a = R
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] a = Union(a, R)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Set[/COLOR] fd = Cells(7, 5)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
i want to exclude range fd from range a. any solution?
To what end?
What will you be DOING with that range once it's created?

I ask because it may be easier to just use the whole range, including the one you want excluded
Then use some other method to just ignore that one cell during whatever process you're running on the whole range.
 

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
This should also do the same as the above code, however, if there is existing data in fd it won't be overwritten.
Also, I'm not sure VBAGeek's code will work correctly if there is already data in Range a

Code:
Sub ddt()
Dim fd As Range, a As Range
    Set a = Range(Cells(1, 1), Cells(20, 10))
    Set fd = Cells(7, 5)
    
    Set RngExc = Nothing
    For Each Cll In a
        If Application.Intersect(Cll, fd) Is Nothing Then
            If RngExc Is Nothing Then
                Set RngExc = Cll
            Else
                Set RngExc = Application.Union(RngExc, Cll)
            End If
        End If
    Next Cll
    RngExc.Value = 1
End Sub
i also knows that using looping to set value one by one. however, this method is too heavy for hardware
 

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
the most efficient code i can think is
Code:
Sub selectme()
Dim a As Range
Dim fd As Range
Set a = Range(Cells(1, 1), Cells(20, 10))
Set fd = Cells(7, 5)
a.Copy Destination:=Cells(1, 11)
a.Value = 1
Cells(1, 11).Copy Destination:=fd
End Sub
but this method is still too complicated if i need to exclude multiple range.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
i also knows that using looping to set value one by one. however, this method is too heavy for hardware
Then I think JonMo raises a good question because there are no built-in features to perform this operation which means the only solution is some form of looping.
If Range "a" because very large (i.e. more than 1M cells) then looping would definitely be an issue, but for smaller ranges such as the one you posted the looping code will actually operate pretty quickly.

EDIT:
Actually, if all you are looking to do is overwrite the values in a (excluding fd) you could do this.
Code:
Sub ddt()
Dim fd As Range, a As Range
Dim arr As Variant
    Set a = Range(Cells(1, 1), Cells(20, 10))
    Set fd = Range(Cells(7, 5), Cells(8, 6))
    
    arr = fd.Value
    
    a.Value = 1
    fd.Value = arr
End Sub
The only requirement to this is that fd must be a contiguous range.

This just sets the entire range of a to whatever you want and then resets fd back to what it was before.
 
Last edited:

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top