vba// how to count rows from particular cell.

[riya]

Hi All,
i have the below data in "A" column, and want to count the rows from A5 cell. i am writing the below code but not able to get.

I= Sheets(2).Range("A1:A5" & Rows.Count).End(xlUp).Row - Error
I = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row - Giving the count of all rows

ABC
XYZ
PNR
MNO
STZ
-

QMP

 

[GraH - Guido]

since you know you start at the fifth row, you could simply do this...

 I = Sheets(1).Range("A1").End(xlDown).Row - 5

But does your data contain blanks? Always better to upload a sample workbook containing your code.

 

[riya]

since you know you start at the fifth row, you could simply do this...

 I = Sheets(1).Range("A1").End(xlDown).Row - 5

But does your data contain blanks? Always better to upload a sample workbook containing your code.

Hi Grah,
Yes i have blanks in the data. Please see the attached sample file

 

[Chihiro]

Something like below?

Dim i
With Range("A5:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    i = Evaluate("=COUNTA(" & .Address & ")")
End With
Debug.Print i

There are many other ways to do this as well.

Dim ar, i As Long, x As Long
ar = Range("A5:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

For i = 1 To UBound(ar)
    If Len(ar(i, 1)) > 0 Then
        x = x + 1
    End If
Next
Debug.Print x

 

[riya]

Something like below?

Dim i
With Range("A5:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    i = Evaluate("=COUNTA(" & .Address & ")")
End With
Debug.Print i

There are many other ways to do this as well.

Dim ar, i As Long, x As Long
ar = Range("A5:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

For i = 1 To UBound(ar)
    If Len(ar(i, 1)) > 0 Then
        x = x + 1
    End If
Next
Debug.Print x

below is perfect
Dim i
With Range("A5:A" & Cells(Rows.Count, 1).End(xlUp).Row)
i = Evaluate("=COUNTA(" & .Address & ")")
End With
Debug.Print i

** i = Evaluate("=COUNTA(" & .Address & ")")** what exact this line is doing ?

 

[Chihiro]

** i = Evaluate("=COUNTA(" & .Address & ")")** what exact this line is doing ?

Evaluate is used to evaluate formula using address range. =COUNTA(Range).
In many cases use of Evaluate and/or Application.Function can simplify codes. And can be more efficient than, custom logic coded using loops etc.

Though looping through variant array is very fast as well (2nd code is one such example). Since all calculation can be done in memory.