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
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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.