If you are looking for ways to subtract one range from another using Excel VBA, then you will find this article useful. So, let’s start with our main article.
3 Cases for Subtract One Range from Another Using Excel VBA
Here, we have the following dataset containing sales records of a company for different products in different regions. We will try to show the subtraction process between two ranges in the following 3 Cases using some VBA codes.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Case-1: Getting Range Address after Subtraction of One Range from Another
In this section, we will specify our two ranges of the Product column in a VBA code and then after subtracting we will get the address of the subtracted range through a message box.
Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.
After that, a Module will be created.
Step-02:
➤ Write 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 SubHere, we have declared full_range, partial_range, general_range, remnant_range, product as Range and then set full_range to the range A4:A13, partial_range to the range A9:A13.
Then, we used Intersect method to get the intersection point between these ranges and then stored this range in the general_range.
FOR loop is used to go through each cell of the range A4:A13 and then two IF statements are used to get the subtracted range between these ranges and which is stored in the remnant_range.
Finally, we will have the address of this range through a message box (MsgBox).
➤ Press F5.
Finally, you will get the address of the range $A$4:$A$8 after subtracting the range $A$4:$A$13 from $A$9:$A$13.
Read More: How to Create a Subtraction Formula in Excel
Case-2: Subtract One Range from Another with Customized VBA Function
In this section, we are going to create a function that will return us the deducted range after giving two ranges of the Product column as inputs to this function.
Steps:
➤ Follow Step-01 of Case-1.
➤ Write 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 FunctionHere, 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 main sheet.
➤ Type the following function in cell E4.
=deductedrange1(B4:B13,B11:B13)Here, our created function deductedrange1 will return the deducted range from B4:B13 to B11:B13.
After pressing ENTER, you will get the subtracted range of the Product column in the Subtracted Range column.
For using other versions except for Microsoft Excel 365, press CTRL+SHIFT+ENTER instead of pressing ENTER.
Read More: Excel formula to find difference between two numbers
Case-3: Subtract One Range from Another with Two Functions Using VBA
Here, we will create a function with help of another function in a VBA code to return subtracted range after subtracting between two ranges in a faster way.
Steps:
➤ Follow Step-01 of Case-1.
➤ Write 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 FunctionHere, we have declared full_range, and partial_range as Range inside the function deductedrange2 as the inputs and the result of this function would be also as a Range. Then, we declared remnant_range, item, general_range as Range and used Intersect method to get the intersection point between these ranges and then stored this range in the general_range.
By using the IF statement you can set remnant_range as the full_range for an empty general_range otherwise for equal ranges in full_range and general_range you will get an empty remnant_range.
For other cases, the FOR loop is used to go through each cell of the range full_range and it will call the function deductedrange3 and store the value in the updated_range.
Finally, we have assigned the function deductedrange2 to the remnant_range.
The other function deductedrange3 will store the deducted range to the updated_range either by splitting the range from top to bottom or from left to right. Inside the deductedrange3 function, we have declared item, general_range, updated_range (optional) as Range. By declaring column_direction as Boolean and depending on conditions we have set it to True or False and for False value, our range will be split row-wise direction (top to bottom) otherwise to the column-wise (left to right) direction.
➤ Save the code and go back to the main sheet.
➤ Type the following function in cell E4.
=deductedrange2(B4:C13,B11:C13)Here, our created function deductedrange2 will return the subtracted range from B4:C13 to B11:C13.
Immediately, after pressing ENTER, you will get the remnant products and regions in the Range1 and Range2 columns.
For using other versions except for Microsoft Excel 365, press CTRL+SHIFT+ENTER instead of pressing ENTER.
Read More: How to Subtract in Excel Based on Criteria
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Download Workbook
Conclusion
In this article, we tried to cover the ways to subtract one range from another using Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
