How to check sum of available values of the column = 0 in Excel

Question

I have an excel with column where the values are ranges from B2:B8, But first i am trying to use the filter then have to find SUM of the available column ranges

I have been using the code in VBA

Actual Data Table

S.No    T-Code       Value   Gcode    Status
1.       B220        2276     1
2.       AC55        2570     2
3.                  -2570     2
4.       D550       -5565     1
5.       N775        5565     1
6.       D887       -7797     3
7.                   2570     1
8.       D220       -2276     1

Script i have been using to use the Autofilter

Set shtFinal = wrkbok.Sheets(1)
Lastrow = shtFinal.Cells(Rows.Count, 1).End(xlUp).Row
shtFinal.Range("A1:Z" & Lastrow).AutoFilter Field:=4, Criteria1:="1"
shtFinal.Range("A1:Z" & Lastrow).AutoFilter Field:=2, Criteria1:="<>"

Table after Filter

S.No    T-Code       Value   Gcode   Status
1.       B220        2276     1
4.       D550       -5565     1
5.       N775        5565     1
8.       D220       -2276     1

Trying the Code to get the Expected output data

FilteredLastRow = shtFinal.Cells(Rows.Count, 1).End(xlUp).Row
If Excel.WorksheetFunction.Sum(shtFinal.Range("D2:D" & FilteredLastRow).Value) = 0 Then
shtFinal.Range("E2:E" & FilteredLastRow ).SpecialCells(xlCellTypeVisible).Value = "Matched"
End If

Expected Output Data

S.No    T-Code       Value   Gcode   Status
1.       B220        2276     1      Matched
4.       D550       -5565     1      Matched
5.       N775        5565     1      Matched
8.       D220       -2276     1      Matched

The below line of code is not working, instead of SUM only the visible rows it use to sum all the rows from 1 to 8 and the IF condition doesn't satisfies.

If Excel.WorksheetFunction.Sum(shtFinal.Range("D2:D" & FilteredLastRow).Value) = 0 Then

Please Suggest.

Answer 1

We can Use Subtotal instead of Sum.

If We want total values only in visible rows, Subtotal 109 is the only option

By Modifying the below line of code it works.

If Application.WorksheetFunction.Subtotal(109, shtFinal.Range("D2:D" & lngTemp)) = 0 Then