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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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

ADVERTISEMENT

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

ADVERTISEMENT

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,148,391
Messages
5,746,435
Members
424,017
Latest member
jaka

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
Top