Using CountA and getting weird results

[AndyGalloway]

I have a spreadsheet that is created from a template. It is used to track work done on behalf of a customer, one piece of work on each line. The customer's details are at the top of the sheet, the table headings are on row 9 and the first invoice-able piece of work goes in row 10. In column F the spreadsheet calculates the total of the invoice, column G is for payments received and Column H shows the outstanding balance. This is also shown in cell B2, just to keep it with all the other customer details at the top of the sheet. As a new row is being populated, the formula in column H is re-written and the formula in cell B2 is rewritten. To do this, I need to know the "Last Row" used in the table.

So, the problem I'm having is that CountA produces slightly weird results. I am using the code below to determine the Line Count i.e. the number of lines in the table, below the headers. CountA works perfectly as long as there is more than one entry in the table. If there is only one entry in the table, then the varLineCount comes back as 2, making the varLastRow 11 instead of 10. I've tried using Target.Row to determine if I am working in Row 10 and setting varLineCount and varLastRow, which works as far as it goes but when I revisit row 10 at a later stage, cell B2 is updated with the column H total from Row 10, not from the last row. Apart from that, it all works perfectly!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim varLineCount As Variant
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    '***********************
    ' Changed 05 Feb 2020
    If Target.Row = 10 Then ' Take account of a CountA anomaly
        varLineCount = 1
        varLastRow = 10
    Else
        varLineCount = WorksheetFunction.CountA(ws.Range("A10", ws.Range("A10").End(xlDown)))
        If Target.Row > varLineCount + 9 Then
            varLastRow = Target.Row
        Else
            varLastRow = varLineCount + 9
        End If
    End If
    '***********************

    varTargetCell = Target.Address

    'Update the sheet if the date in column A or B is entered or changes
    If Not Application.Intersect(Range("A10:B" & varLastRow), Range(varTargetCell)) Is Nothing Then
        varActiveRow = Target.Row
        Update_LeaderSheet
    End If
    
    'Update the sheet if the amounts in column E, F or G change
    If Not Application.Intersect(Range("E10:H" & varLastRow), Range(varTargetCell)) Is Nothing Then
        varActiveRow = Target.Row
        Update_LeaderSheet
    End If

End Sub

' In Module1

Public varTargetCell As Variant
Public varActiveRow As Single
Public varLastRow As Single
Public varSheet As String

Sub Update_LeaderSheet()
    
    ' Changed 05 Feb 2020
    If Range("A" & varActiveRow) = "" Then
        Range("B" & varActiveRow) = ""
    Else
        Range("B" & varActiveRow).FormulaR1C1 = "=TEXT(RC[-1],""mmmm yyyy"")"
   '     Range("B" & varActiveRow) = "=Concat(Year(A" & varActiveRow & "),"" "",Year(A" & varActiveRow & ")"
    End If
    If Range("E" & varActiveRow) = "" Then
        Range("H" & varActiveRow) = ""
    Else
        If varActiveRow = 10 Then
            Range("H" & varActiveRow) = "=E" & varActiveRow & "-G" & varActiveRow
        Else
            Range("H" & varActiveRow) = "=H" & varActiveRow - 1 & "+E" & varActiveRow & "-G" & varActiveRow
        End If
    End If
    Range("B3") = "=H" & varLastRow

End Sub

 

[RoryA]

Right - that is why CountA returns 2 then.

 

[AndyGalloway]

Yup. Thanks for your help