Calling CurrentRegion to Inspect a List's Useful Characteristics

 
Sub CurrentRegionExample() 
    Dim rg As Range 
    Dim myWorksheet As Worksheet 

    Set myWorksheet = ThisWorkbook.Worksheets("Sheet1") 
    Set rg = myWorksheet.Cells(1, 1).CurrentRegion 
    myWorksheet.Range("I2").Value = rg.ListHeaderRows 
    myWorksheet.Range("I3").Value = rg.Columns.Count 

    Set rg = rg.Resize(rg.Rows.Count - rg.ListHeaderRows,rg.Columns.Count).Offset(1, 0) 
    Debug.Print rg.Rows.Count 
    Debug.Print rg.Cells.Count 
    Debug.Print Application.WorksheetFunction.CountBlank(rg) 
    Debug.Print Application.WorksheetFunction.Count(rg) 
    Debug.Print rg.Rows.Count + rg.Cells(1, 1).Row - 1 

    Set rg = Nothing 
    Set myWorksheet = Nothing 
End Sub

Related examples in the same category

1.	Select entire sheet
2.	Set cell value with For Loop
3.	Use row and column index to reference cell
4.	Cells(Rows.Count, "A").End(xlUp).Select
5.	looping through a worksheet range using a For/Next loop.
6.	Cells.Find: Get Real Last Cell
7.	Get cell value
8.	Sum Cells Based on the Interior Color
9.	Entering a value in the next empty cell
10.	Use the AutoFill function by specifying the destination range
11.	Using Replace Programmatically to Set the Correct Range

Calling CurrentRegion to Inspect a List's Useful Characteristics : Cells « Excel « VBA / Excel / Access / Word

Related examples in the same category