Excel VBA Intersection Range Tricks

Excel VBA supports Intersection and Unions of Ranges. Sadly, it doesn’t support inversion of ranges. Anyway, here are some useful things built on Intersection. (I use Excel Macros to get stuff done and really don’t care a twit about understanding the nuances of yet another programming language — be carefully with these).

Intersection returns a range which is the common area of all the ranges given as an argument to the Intersection function. Using range.EntireRow or range.EntireColumn lets you pick out cells within a target range by columns or rows of the range.


Function GetRowsOfRange(startRow As Integer, endRow As Integer, rng As Range) As Range
         Dim fullRange As Range    Set fullRange = Range(rng.Cells(startRow, 1), rng.Cells(endRow, 1))
         Set GetRowsOfRange = Application.Intersect(fullRange.EntireRow, rng)
End Function

Function GetColsOfRange(startCol As Integer, endCol As Integer, rng As Range) As Range
         Dim fullRange As Range
         Set fullRange = Range(rng.Cells(1, startCol), rng.Cells(1, endCol))
         Set GetColsOfRange = Application.Intersect(fullRange.EntireColumn, rng)
End Function

The Range.Offset function shifts the entire range by the offset. Combining this with Intersection lets you remove rows or columns from a target range. Here are some subroutines that illustrate this.


Function BumpDown(numRows As Integer, rng As Range) As Range
     Set BumpDown = rng.Offset(numRows, 0)
End Function

Function RemoveRowsFromTopOfRange(numRows As Integer, rng As Range) As Range
     Set RemoveRowsFromTopOfRange = Application.Intersect(rng.Offset(numRows, 0), rng)
End Function

Function RemoveColsFromLeftOfRange(numCols As Integer, rng As Range) As Range
    Set RemoveColsFromLeftOfRange = Application.Intersect(rng.Offset(0, numCols), rng)
End Function

End