How to Select Dynamic Used Range in Excel VBA

VBA Macro to Select the Dynamic Actual UsedRange in Excel
Generally in most of the cases the Used Range is always dynamic.In this case the UsedRange.Selection method is not suitable.

So, we will use the following method to find the Actual Dynamic UsedRange.

Sub DynamicUsedRange(ByRef MyRange )
        Dim DataLastCell As Object
        Dim WS As Worksheet
        Dim MyRng
        
        Set WS = ActiveSheet
   
        'To Find the Last Row Number with data in the Range
        Set DataLastCell = WS.Cells.Find("*", WS.Cells(1, 1), , , xlByRows, xlPrevious)
        RC= DataLastCell.Row
        RC =  WS.Cells(Rows.Count, "A").End(xlUp).Row
          
        'To Find the Last Column Number with data in the Range
        Set DataLastCell = WS.Cells.Find("*", WS.Cells(1, 1), , , xlByColumns, xlPrevious)
        CC= DataLastCell.Column
        CC = WS.Cells(1, Columns.Count).End(xlToLeft).Column

        'To Find the Filtered Rows Count based on First Column with in the Filtered Range 
         FRC = WS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).
         Cells.Count) - 1
        
        'To Find the End of the Range in  Data
        DR = DataLastCell.Address
        
   Set MyRng = WS.Range("A2:" & DR).Select
         MyRange = MyRng.Address   

        'To Find the Last Column Index Name in the Range
        CN = Split(Cells(, CC).Address, "$")(1)        
  Set MyRng = WS.Range("A2:" & CN & RC).Select
        MyRange = MyRng.Address

End Sub

Eg :

Note :
To select a Range from Active selection
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
Range(Selection, Selection.End(xlDown).Offset(0, 4)).Select
Resizing a Range :
Set rng = Range("A2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1, 4)

To Find the Last Row and Last Column Numbers in used range with data from a column say 'A', we can find as follows :
RC =  WS.Cells(Rows.Count, "A").End(xlUp).Row
           'Range("A" & Rows.Count).End(xlUp).Row

CC = WS.Cells(1, Columns.Count).End(xlToLeft).Column

To Find the Count of Filtered Rows with data from based on column say 'A',  :
FRC = WS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count) - 1

Example Macro :
Copying the Dynamic Used Range from One Sheet to Another Sheet 

Sub Calling_DynRange(ByRef MyRange)

Dim WS as Object
Dim SrcSht as Object
Dim TgtSht as Object

Application.Calculation = xlManual
Application.DisplayAlerts = False

Set WS = ActiveSheet
Set SrcSht = ThisWorkbook.Sheets("Source_Sheet")
Set TgtSht  = ThisWorkbook.Sheets("Target_Sheet")

SrcSht.Activate
Call DynamicUsedRange(MyRange)
Range(MyRange).Select

Selection.Copy
'RC = (Selection.Rows.Count) ' Dynamic Used Range Rows Count

TgtSht.Activate
TgtSht .Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'TgtSht.Activate
'Range(MyRange).Copy Destination:=Range("A2")

Set WS=Nothing
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
End Sub

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------