how to count the number of rows with data from a specific cell in excel vba

user_15672172 0
Viewed 965

I am just learning VBA excel. I am writing a code to count number of rows with data from specific cell say B5

the code as follows.

Sub Count_Rows()

    Dim No_Of_Rows As Integer
    No_Of_Rows = Range("B5").End(xlDown).Rows.Count
    MsgBox No_Of_Rows
End Sub

the above code always returns the value 1.

vba excel
2 Answers

Using End

  • Range("B5").End(xlDown) is a reference to the cell before the first following empty cell. So it has only one row. You want to return the number of rows of the range from the first cell to this cell.

The More Reliable xlUp

This will 'look' from the bottom-most cell of the worksheet upward and find the first empty cell to use the range to return the number of rows.

Sub countRowsUp()
    Dim No_Of_Rows As Long
    No_Of_Rows = Range("B5", Range("B" & Rows.Count).End(xlUp)).Rows.Count
    'No_Of_Rows = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp)).Rows.Count
    MsgBox No_Of_Rows
End Sub

The Less Reliable xlDown

This will 'look' from the given cell downward and find the first empty cell to use the range to return the number of rows.

Sub countRowsDown()
    Dim No_Of_Rows As Long
    No_Of_Rows = Range("B5", Range("B5").End(xlDown)).Rows.Count
    'No_Of_Rows = Range(Range("B5"), Range("B5").End(xlDown)).Rows.Count
    MsgBox No_Of_Rows
End Sub
user_98140690

It is always returning 1 because you are counting the number of rows in a range that you have defined as a single cell. If you want the value from a specific cell you use .Range("B5").Value and if you want to check if all the rows in a certain set of cells have that value (lets say B10-B100) you read it into a variable and loop through it:

Dim wS As Worksheet
Dim searchArea as Range
Dim c as Range
Dim vl As Variant
Dim No_Of_Rows as Integer

Set wS = ActiveSheet
Set searchArea = wS.Range("B10:B100")
vl = wS.Range("B5").Value
No_Of_Rows = 0

For Each c in searchArea
    If (c.Value = vl) Then
        No_Of_Rows = No_Of_Rows + 1
    End If
Next

MsgBox No_Of_Rows

This might have slight syntax errors so if it doesn't run I will clarify/correct.

user_125066850
Related