Subtracting One Range from Another in Excel VBA

The following dataset contains sales records of different products in different regions.

Example 1 – Getting a Range Address after Subtracting of One Range from Another

Step 1:

Go to the Developer Tab >> Visual Basic.

In the Visual Basic Editor, go to the Insert tab.
Choose Module.

A Module is created.

Step 2:

Enter the following code:

Sub deduction_of_ranges_1()
Dim full_range, partial_range, general_range, remnant_range As Range
Dim product As Range
Set full_range = Range("A4:A13")
Set partial_range = Range("A9:A13")
Set general_range = Intersect(full_range, partial_range)
For Each product In full_range
If Intersect(general_range, product) Is Nothing Then
If remnant_range Is Nothing Then
Set remnant_range = product
Else
Set remnant_range = Application.Union(remnant_range, product)
End If
End If
Next product
MsgBox remnant_range.Address
End Sub

full_range, partial_range, general_range, remnant_range, product are declared as Range and full_range is set to A4:A13, and partial_range to the A9:A13.
The Intersect method gets the intersection point between these ranges and stores it in general_range.
A FOR loop is used to go through each cell in A4:A13 and two IF statements are used to get the subtracted range which is stored in remnant_range.
The address of this range is displayed in a message box (MsgBox).

Press F5.

You will get the address of $A$4:$A$8 after subtracting $A$4:$A$13 from $A$9:$A$13.

Example 2 – Subtract One Range from Another with a Customized VBA Function

Steps:

Follow Step 1 in Example 1.
Enter the following code.

Function deductedrange1(full_range As Range, partial_range As Range) As Range
Dim remnant_range As Range
Set remnant_range = Nothing
Dim item As Range
For Each item In full_range
If Intersect(item, partial_range) Is Nothing Then
If remnant_range Is Nothing Then
Set remnant_range = item
Else
Set remnant_range = Union(item, remnant_range)
End If
End If
Next item
Set deductedrange1 = remnant_range
End Function

Here, we have declared full_range, partial_range as Range inside the function as the inputs, and the result of this function would be also as a Range, and remnant_range, item as Range.
FOR loop is used to go through each cell of the range full_range and then two IF statements (first IF statement will check if the cell of the full_range is within the partial_range or not, second IF statement will check if the remnant_range has any value or not) is used to get the subtracted range between the given two ranges and which is stored to the remnant_range.
Finally, we have assigned the function deductedrange1 to the remnant_range.

Save the code and go back to the worksheet.
Enter the following function in E4.

=deductedrange1(B4:B13,B11:B13)

The function deductedrange1 is created. It will return the subtracted range from B4:B13 and B11:B13.

Press ENTER.

This is the output.

For older Excel versions, press CTRL+SHIFT+ENTER instead of ENTER.

Example 3 – Subtract One Range from Another with Two Functions Using VBA

Steps:

Follow Step 1 in Example 1.
Enter the following code.

Function deductedrange2(full_range As Range, partial_range As Range) As Range
Dim remnant_range As Range, item As Range
Dim general_range As Range
Set general_range = Intersect(full_range, partial_range)
Set updated_range = Nothing
If general_range Is Nothing Then
Set remnant_range = full_range
ElseIf general_range.Address = full_range.Address Then
Set remnant_range = Nothing
Else
For Each item In full_range.Areas
Set updated_range = deductedrange3(item, general_range)
Next item
Set remnant_range = updated_range
End If
Set deductedrange2 = remnant_range
End Function

Function deductedrange3(item As Range, general_range As Range, _
Optional updated_range As Range = Nothing) As Range
Dim side1 As Range, side2 As Range, side3 As Range, side4 As Range
Dim general_range1 As Range
Dim column_direction As Boolean
Set general_range1 = Intersect(item, general_range)
If general_range1 Is Nothing Then
If updated_range Is Nothing Then
Set updated_range = item
Else
Set updated_range = Union(updated_range, item)
End If
ElseIf Not general_range1.Address = item.Address Then
If Not item.Cells.CountLarge = 1 Then
If Not general_range1.Columns.Count = item.Columns.Count And _
((Not general_range1.Cells.CountLarge = 1 And _
(general_range1.Rows.Count > general_range1.Columns.Count _
And item.Columns.Count > 1) Or (general_range1.Rows.Count = 1 _
And Not item.Columns.Count = 1)) Or _
(general_range1.Cells.CountLarge = 1 _
And item.Columns.Count > item.Rows.Count)) Then
column_direction = True
Else
column_direction = False
End If
If Not column_direction Then
Set side1 = item.Resize(item.Rows.Count \ 2)
Set side2 = item.Resize(item.Rows.Count - _
side1.Rows.Count).Offset(side1.Rows.Count)
Set updated_range = deductedrange3(side1, general_range1, updated_range)
Set updated_range = deductedrange3(side2, general_range1, updated_range)
Else
Set side3 = item.Resize(, item.Columns.Count \ 2)
Set side4 = item.Resize(, item.Columns.Count - _
side3.Columns.Count).Offset(, side3.Columns.Count)
Set updated_range = deductedrange3(side3, general_range1, updated_range)
Set updated_range = deductedrange3(side4, general_range1, updated_range)
End If
End If
End If
Set deductedrange3 = updated_range
End Function

full_range, and partial_range are declared as Range inside the function deductedrange2 (inputs and result will also be a Range). remnant_range, item, general_range are declared as Range and the Intersect method is used to get the intersection point between these ranges. It stores this range in general_range.
The IF statement sets remnant_range as the full_range for an empty general_range. Otherwise, it returns an empty remnant_range.
The FOR loop is used to go through each cell in full_range and the function deductedrange3 is called and stores the value in updated_range.
The function deductedrange2 is assigned to the remnant_range.
deductedrange3 will store the deducted range in updated_range either by splitting the range from top to bottom or from left to right. Inside the deductedrange3 function, item, general_range, updated_range (optional) are declared as Range. column_direction is declared as Boolean and depending on the conditions, it is set to True or False. If False, the range will be split row-wise (top to bottom). Otherwise, column-wise (left to right).