Count number of rows which contain text

[Nick70]

Hi,

I need to have a VBA code that looks at rows in a range and gives back the number of rows which contain text in any of their cells (only text not numbers).

For example for range(B3:H10) if there is text in cells C3, F3, D9, G10, H10 then the number I should get is 3 because we have text in row B3:H3, in row B9:H9 and in row B10:H10.

The result can be found in cell A2.

How would I do that?

Thanks,
Nic

 

[arthurbr]

Formula version

=sumproduct(--ISTEXT(B3:H10))

 

[Nick70]

Hi,

This formula returns number of CELLS in range(B3:H10) which have text in this case 5.
I need number of ROWS which contain text in this specific case 3.

Thanks,
Nic

 

[Akuini]

Maybe this:

Sub try_1()
Dim c As Range
Dim q As Long
With Range("B3:H10")
    q = .Columns.Count
    For Each c In .Columns(1).Cells
        If WorksheetFunction.CountA(c.Resize(1, q)) > 0 Then n = n + 1
    Next
End With
Range("A2") = n
End Sub

 

[Fluff]

Or with a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	3
3			a			a
4
5			1
6
7				1
8						1
9				a		1
10							a	a
Data

Cell Formulas
Range	Formula
A2	A2	=SUM(BYROW(B3:H10,LAMBDA(br,SIGN(SUM(--ISTEXT(br))))))

 

[Nick70]

Both great solutions thanks!
Nic

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.