exclude a range from another range

[ww4612]

hello
i try to exclude one range from another range.

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?

 

[Jonmo1]

You can put the values of the 'excluded' range into an array
Do whatever it is you're going to do to the whole range (including the excluded range)
Then put the values from the array back into the excluded range

Example

Sub test()
Dim myrange As Variant
myrange = Range("B2:C3") 'Excluded Range
Range("A1:D4").Value = 1 'Whole Range
Range("B2:C3").Value = myrange
End Sub

 

[ww4612]

You can put the values of the 'excluded' range into an array
Do whatever it is you're going to do to the whole range (including the excluded range)
Then put the values from the array back into the excluded range

Example

Sub test()
Dim myrange As Variant
myrange = Range("B2:C3") 'Excluded Range
Range("A1:D4").Value = 1 'Whole Range
Range("B2:C3").Value = myrange
End Sub

well. it seems that this is the only solution so far before the next version of VBA

 

[Jonmo1]

I wouldn't say it's the 'only' solution.
Other solutions have been posted.
And you've chosen not to use them (for valid reasons)

And I wouldn't count on the next version of VBA to inculde some function like that.

 

[Jonmo1]

You might also ask yourself this..

WHY is that range excluded?
What is it about that range that that is different from the rest of the range and made you decide it should be excluded?
Can that information be used somehow?
Perhaps by the AutoFilter