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