Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

[DocAElstein]

Second solution for this Thread
https://excelfox.com/forum/showthrea...sult-is-change

Looking at jindon’s solution at the cross post:
https://www.excelforum.com/excel-pro...is-change.html

Jindon has done a function to do the summing of the columns G and J

He sugest that, for example you place then in cell J35
=SumIfClear(J16:J34) https://www.excelforum.com/excel-pro...ml#post5386274

What this does is , taken in the column range, rng , and return the sum value as required.
It does it like this:
' make a range object , x , of a few areas, each area being a row with a “shape with a name Like "Line*"
' The sum calculation is then done only taking row values in the column, range , rng , which do not intersect with the range of rows with a shape, x

Option Explicit
' https://www.excelforum.com/excel-programming-vba-macros/1325405-reserve-the-horizontal-line-numbers-and-information-but-the-calculation-result-is-change.html
Sub CallSumIfClear()
 Call SumIfClear(Range("J16:J34"))

End Sub
Function SumIfClear(rng As Range) As Double
    Dim r As Range, x As Range, Sp As Shape
    'Application.Volatile
' make a range object of a few areas, each area being a row with a shape with a name Like "Line*"
    For Each Sp In rng.Worksheet.Shapes
        If Sp.Name Like "Line*" Then
            If x Is Nothing Then
                Set x = Range(Sp.TopLeftCell, Sp.BottomRightCell)
            Else
                Set x = Union(x, Range(Sp.TopLeftCell, Sp.BottomRightCell))
            End If
        End If
    Next
' The sum calculation
    For Each r In rng
        If Intersect(r, x) Is Nothing Then SumIfClear = SumIfClear + Val(r.Value)
    Next
End Function

( The formula given by Jindon is no good as it does not answer the question )

---

Jindon’s formula has shown me how to determine where shapes ( like a line ) are.
So I could, for example, build a string of the row numbers with a shape in

For example this next macro , will return, for the sample data, in the variable, strLnRws ,
__18__21__

Sub BuildStingOfRowsWithShapeLine()
Dim strLnRws As String: Let strLnRws = " "
Dim RngG As Range: Set RngG = Range("G16:G34")
Dim Sp As Shape
    For Each Sp In RngG.Worksheet.Shapes
        If Sp.Name Like "Line*" Then
         Let strLnRws = strLnRws & Sp.TopLeftCell.Row & " "
        Else
        End If
    Next
Debug.Print strLnRws ' From VB Editor ,  hit keys  Ctrl + g to get the immediate window to see the contents
End Sub

I can check for the rows so as not to sum those rows. ( Note I will check for a string of “ “ & TheRowNumber & “ “ , as this will avoid errors caused by checking for , for example 3 , when I have a row of 436 : If I checked for 3 , I would find it if I had 436 , which would be incorrect )

For example, the Instr function can be used to see if a row number is present in that strLnRws. Thuis is implimented in the example below to get the sum for column G

Sub BuildStingOfRowsWithShapeLineAndSumColumnIfNoShapeLine()
Dim strLnRws As String: Let strLnRws = " "
Dim RngG As Range: Set RngG = Range("G16:G34")
Dim Sp As Shape
    For Each Sp In RngG.Worksheet.Shapes
        If Sp.Name Like "Line*" Then
         Let strLnRws = strLnRws & Sp.TopLeftCell.Row & " "
        Else
        End If
    Next
Debug.Print strLnRws ' From VB Editor ,  hit keys  Ctrl + g to get the immediate window to see the contents

Dim Cnt
    For Cnt = 1 To RngG.Rows.Count Step 1
    Dim SumG As Double
        If InStr(1, strLnRws, " " & RngG.Item(Cnt).Row & " ", vbBinaryCompare) = 0 And RngG.Item(Cnt).Value2 <> "" Then  '   InStr  will return a  0  if the rows number is not present in the string  strLnRws
         Let SumG = SumG + RngG.Item(Cnt).Value2
        Else
        ' there is no value or it is struck through
        End If
    Next Cnt
Debug.Print SumG
End Sub

---

Using the above information we can write a second event coding macro which this time will work on the original worksheet: there is no longer a need to modify the range to have strikethroughs:
See next post