Intersect

Most of the time while working with array or range, you might have got the situation where you need to exclude headers from your range reference. there are couple ways of doing it. but the best way is to get the common range by using intersect function.

Intersect function is an inbuilt VBA function, which return a range object.

Have a look at the data table below.

Now, we need to create a range reference in vba with this data except the headers. lets try to write a code

Join my VBA whatsApp Group to know more

Sub ExcludeHeadersByUsingIntersect()
    
    Dim rngRange As Range
    Set rngRange = Sheet1.Range("A1").CurrentRegion
    Set rngRange = Intersect(rngRange, rngRange.Offset(1))
    rngRange.Select
    
End Sub

Second last line of that code will select the range excluding header, now we can use this rngRange object to copy, cut, color this range.

In this code, we are also using two more important function which return range object.

1. CurrentRegion
2. Offset

See the comments in this code to get more understanding.

Sub ExcludeHeadersByUsingIntersect()
    
    Dim rngRange As Range

    '--CurrentRegion is the property of Range object which will
    '--Return Range object. so this next line will return all the
    '--connected cells with A1. currentRegion will consider all cells
    '-- together until it found a completely blank row or column in between data.

    Set rngRange = Sheet1.Range("A1").CurrentRegion

    '--rngRange is not referring all cells around Range("A1").
    '--Offset will return one more range with same rows and columns count
    '-- but will exclude first row and will include one more row at the bottom
    ' and that's how we get two range to get the common area from.

    '-- Interect will give return common area of two or more ranges.
    '--it will return Nothing, is there is not intersection or common area found.   
    Set rngRange = Intersect(rngRange, rngRange.Offset(1))
    rngRange.Select
    
End Sub

Quick-Guide-First-Part.pdf (1703 downloads)