Monday, 13 April 2015

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 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
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")

Call DynamicUsedRange(MyRange)

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

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

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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts