Build vertical summary from Columns with Count

January 12, 2024

I have Sheet1 which has a 1 in each column where criteria is met for that flag. Sheet3 is a sumary sheet that I would like to have the column names and the total of that column (ie: does not = null) and put them into Sheet3 for a vertical summary. I was going to do a VLookUp but since all of my process is in vba, I’d like to keep all in the code.

>Solution :

The code generates a header if Sheet3 is blank; otherwise, the summation result is appended at the end.

Option Explicit
Sub Demo2()
    Dim rngData As Range
    Dim i As Long, j As Long, iR As Long
    Dim arrData, arrRes
    Const COL_START = 4 ' the 1st NI reason col on Sheet1
    ' load data
    Set rngData = Sheets("Sheet1").Range("A1").CurrentRegion
    arrData = rngData.Value
    ReDim arrRes(1 To UBound(arrData, 2), 1)
    iR = 0
    ' loop through data
    For i = COL_START To UBound(arrData, 2)
        iR = iR + 1
        arrRes(iR, 0) = arrData(1, i)
        ' sum up
        For j = LBound(arrData) + 1 To UBound(arrData)
            arrRes(iR, 1) = arrData(j, i) + arrRes(iR, 1)
        Next
    Next i
    ' write output to sheet3
    With Sheets("sheet3")
        i = .Cells(.Rows.Count, 1).End(xlUp).Row
        ' populate header if sheet3 is blank
        If i = 1 Then
            .Range("A1:B1").Value = Array("NI Reason", "Totals")
        End If
        .Range("A" & i + 1).Resize(iR, 2).Value = arrRes
    End With
End Sub