Excel VBA Macros to Know Current Region , Used Range , Last Row , Last Column and Last Cell
------------------------------------------------------------------------------------
Macro To Select Current Region:Sub CurrentRegion()
ActiveSheet.Range("a1").Select
ActiveCell.CurrentRegion.Select
End Sub
< or >
Sub Current_Region()
ActiveSheet.Cells.CurrentRegion.Select
End Sub
Note:
This Macro only selects current region where there is continuity in the range and ignores the remaining data in the sheet.
------------------------------------------------------------------------------------
Macro To Know Used Range In a ActiveSheet :
Model-1:
Sub UsedRange()
LastUsedRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).RowLastUsedColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
' Last Row and Column based on Particular Column or Row :
' LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row
' LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
' LastColumn = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
' LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "You Have Used " & LastUsedRow & " Rows " & vbCrLf & _
" and " & LastUsedColumn & " Columns In This Worksheet"
End Sub
Model-II :
Sub Used_Rows_Columns_Range()Dim RC As Long
Dim CC As Long
Dim Sort_Range As String
'To Get Column Index Number in Used Range(Eg: 1,2,3)
RC = ActiveSheet.UsedRange.Rows.Count
CC = ActiveSheet.UsedRange.Columns.Count
MsgBox RC
MsgBox CC
' To Get Column Index Name in Used Range(Eg: A,B,C..)
CN = Split(Cells(, CC).Address, "$")(1)
Data_Range = "$A$2" & " : " & "$" & CN & "$" & RC
MsgBox Data_Range
ActiveSheet.Range(Data_Range).Select
Selection.Copy
'To select the Last cell in the UsedRange
ActiveCell.SpecialCells(xlLastCell).Select
End Sub
Note:
This Macro show entire range or region that you have used in the past or present in the active sheet even you may erases some cells data , they also comes under used range.
------------------------------------------------------------------------------------
Macro To Select Last Used Cell In a Column RangeSub LastUsed_Column_Cell()
ActiveSheet.Range("A1").End(xlDown).Select
ActiveSheet.Range("A10000").End(xlUp).Select
End Sub
Note:
This Macro only selects current region in the column where there is continuity in the column range and ignores the remaining data in the column as shown in the above.
Other References for Dynamic Used Range :
How to Select Dynamic Actual Used Range in Excel
Thanks.,
Tamatam
No comments:
Post a Comment
Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.