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






  1. VBA / Excel / Access / Word
  2. Excel
  3. Cells

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