find a value in a range vba code example

Example 1: excel vba get a range of full columns

To get a contiguous range of entire rows, use this VBA function:

Function WSRows(ws As Worksheet, Row1&, Rows&)
    Set WSRows = ws.Rows(Row1).Resize(Rows)
End Function
  
'-----------------------------------------------------------------
  
MsgBox WSRows(Sheet1, 11, 9).Address		'<--displays: $11:$20
  
'To get the values in the range into a variant array:  
v =  WSRows(Sheet1, 11, 9)
   
'-----------------------------------------------------------------
 
  
'Sister function to a get range of contiguous full columns:  
  
Function WSCols(ws As Worksheet, Col1&, Cols&)
    Set WSCols = ws.Columns(Col1).Resize(, Cols)
End Function

Example 2: count number of cell ina range vba

Sub DisplayColumnCount() 
    Dim iAreaCount As Integer 
    Dim i As Integer 
 
    Worksheets("Sheet1").Activate 
    iAreaCount = Selection.Areas.Count 
 
    If iAreaCount <= 1 Then 
        MsgBox "The selection contains " & Selection.Columns.Count & " columns." 
    Else 
        For i = 1 To iAreaCount 
            MsgBox "Area " & i & " of the selection contains " & _ 
            Selection.Areas(i).Columns.Count & " columns." 
        Next i 
    End If 
End Sub