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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top