VBA - CountA formula with Unknown Rows

[Plokimu77]

Good Afternoon,

How do I tweak this line of code, to count the number of non-blanks in a column, with an unknown number of rows in a range?

ActiveCell.FormulaR1C1 = "=COUNTA(R[-3]C:R[-1]C)"

I tried to record the macro and used the Relative References, however it did not work.

The amount of rows with items varies.

Here is an example of a result with "3" in Cell B4 and "4" in Cell B9.

Thank you

	A	B
1	Apple	4
2	Banana	15
3	Orange	5
4		3
5	Johnny	10
6	Mike	25
7	Melanie	3
8	Philip	2
9		4

 

[BigBeachBananas]

Are you looking for a macro or formula solution?

 

[Plokimu77]

A Macro please

 

[BigBeachBananas]

How about this?

Sub CountAwithUknownRows()

Dim lastRow As Long
Dim cell As Range
Dim rng As Range

    lastRow = Cells(Rows.count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & lastRow)
    For Each cell In rng
        If Not IsEmpty(cell) Then
            count = count + 1
        End If
 
    Next cell
    Cells(lastRow+1, 2).Value = count
 
End Sub

 

[Plokimu77]

Almost, it's overwriting the result on the last entry.

Can we have it give the result in the empty cell?

Thank you

 

[BigBeachBananas]

Try this.

Sub CountAwithUknownRows2()
    Dim lastRow As Long
    Dim cell As Range
    Dim rng As Range
    Dim count As Long

    count = 0
    lastRow = Cells(Rows.count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & lastRow)

    For Each cell In rng
        If Not IsEmpty(cell) Then
            count = count + 1
        Else
            cell.Offset(0, 1).Value = count
            count = 0
        End If
    Next cell

    If count > 0 Then
        rng.Cells(rng.Cells.count).Offset(1, 1).Value = count
    End If
End Sub

 

[Plokimu77]

Excellent.

You saved the day.

Thank you