How to Find Current Region,Used Range,Last Row and Last Column in Excel with VBA Macro

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).Row
LastUsedColumn = 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 Range :
Sub 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 ; Business Intelligence & Analytics Professional 

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