VBA Count if empty/blank and output in the top row - loop columns array

Question

I want to make a macro that will be summing the non blank in a column range (and I want it to loop in a column's array) and show the output in the top row (row 12).

I used this post Sum range-loop in VBA but couldn't apply for my case.

So far I've done this:

Sub Count_blanks()
    Dim arrControlSheet As Variant
    Dim SumErrors As Integer
    Dim TtlErrors As Double
    Dim h As Long

    arrControlSheet = Array("D", "E", "F", "G", "H", "I", "J", "K", "L")

    For h = LBound(arrControlSheet) To UBound(arrControlSheet)
        With Sheets("Control")
            TtlErrors = Cells(12, arrControlSheet(h)) = Application.CountA(Range(Cells(13, arrControlSheet(h)), Cells(80, arrControlSheet(h))))
        .Range(arrControlSheet(h) & 12) = TtlErrors
        End With
    Next
End Sub

Please see the below screenshot to have better visualization:

The macro is supposed to update the row 12, indicating the number of non-blank cells in that respective column (from row 13 to 80).

Thank you very much

Should it be TtlErrors = Application.CountA(Range(Cells(13, arrControlSheet(h)), Cells(80, arrControlSheet(h))))? You have two equals signs in that line. — SJR
– SJR, Commented May 31, 2018 at 15:32
You want a macro. Why not formula =COUNTA(D13:D65536) ? Just curious. — dcromley
– dcromley, Commented May 31, 2018 at 15:47
@dcromley thanks for the suggestion, for this case I wanted to use vba, but thanks :) — Martim On Fire
– Martim On Fire, Commented Jun 1, 2018 at 16:02

r_hudson · Accepted Answer · 2018-05-31 16:36:06Z

1

Sub CalculateNonBlank()

Dim n As Integer

For i = 4 To 12

n = Application.WorksheetFunction.CountA(Range(Cells(14, i), Cells(Rows.Count, i))) Cells(12, i).Value = n

Next

End Sub

answered May 31, 2018 at 16:36

r_hudson

1938 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Count if empty/blank and output in the top row - loop columns array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related