How To Store Current Range Selection using VBA in Excel?

Q. How To Store Current Selection using VBA in Excel?

A. Use the foloowing VBA script:

Option Explicit
Private Sub Example()
    Dim ActSheet As Worksheet
    Dim SelRange As Range
 
    Set ActSheet = ActiveSheet
    Set SelRange = Selection
 
    '' Any code here
    'Dim NewSheet As Worksheet
    '
    'ActiveSheet.Range("A1").Select
    '
    'Set NewSheet = ThisWorkbook.Sheets().Add()
    'NewSheet.Move After:=Sheets(ThisWorkbook.Sheets().Count)

    ActSheet.Select
    SelRange.Select
End Sub

Let's discuss how it works. First, force explicit declaration of all variables:

Option Explicit

To store selection we need two variables (Worksheet and Range):

Dim ActSheet As Worksheet
Dim SelRange As Range

Then we store active Worksheet and current range selection:

Set ActSheet = ActiveSheet
Set SelRange = Selection

Now we can use any VBA code (add new sheets, select or hide cells etc) and then restore origin selection:

ActSheet.Select
SelRange.Select

Using Intersection to Create a Range in Excel VBA

Intersect Method returns a Range object that represents the Intersection of Ranges.

Example selects the Intersection of 2 Ranges (A1:D5 and C3:C10). If the Intersection is blank, the example displays a message box:

Private Sub UseIntersection()
    IntersectRanges Range("A1:D5"), Range("C3:C10")
End Sub
 
Private Sub IntersectRanges(range1 As Range, range2 As Range)
    Dim intRange As Range
 
    ' Application.Intersect Method
    Set intRange = Application.Intersect(range1, range2)
 
    If intRange Is Nothing Then
        ' No Intersection
        MsgBox "Ranges Do Not Intersect!"
    Else
        ' Show new Range's address
        MsgBox (intRange.Address)
 
        ' Select new Range
        intRange.Select
    End If
End Sub