How to deselect part of a selected range in VBA

August 31, 2023

I am attempting to make a macro that selects a entire range to copy into an email, but I’d like to remove the blank cells from the range selection. With that, I’d also like to add its not just blank cells I am removing, it is also the headers of a specific section of the excel document. Ex. If A3:A15 is blank, remove A1:A15 From the selected Range("A1:Z90")

Dim DefaultRange as Range
Set DefaultRange = WS.Range("A1:L62")

'Here is what I need
If ISBLANK(WS.Range("B11:K17")) Then
    'Remove Range("A8:K18") from DefaultRange.
    'New DefaultRange = WS.Range("A1:A7,A19:L62")
End If

I am unable to find any documentation on a method or function that allows me to do this.

>Solution :

You can use the following function to remove one range from another:

Function RemoveRange(MainRange As Range, SubRange As Range) As Range
    
    Dim cl As Range
     
    For Each cl In MainRange.Cells
        If Intersect(cl, SubRange) Is Nothing Then
            If RemoveRange Is Nothing Then
                Set RemoveRange = cl
            Else
                Set RemoveRange = Application.Union(RemoveRange, cl)
            End If
        End If
    Next
    
End Function

So, to remove Range("A8:K18") from DefaultRange you would use it like so:

Set DefaultRange = RemoveRange(DefaultRange, Range("A8:K18"))