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]

Can't you use an actual table? If you do that, you shouldn't need this code at all.

 

[AndyGalloway]

No. These lists are going to be added to every few days on into the future. They will eventually hold hundreds or thousands of records. Everything works well after the user has added their second record, it's only when they start a new sheet and put the first record in it that they have a problem.

Besides, I really want to know what is wrong with CountA. Is there a bug with it or am I using it incorrectly?

 

[RoryA]

That sounds like a reason for using a table, not a reason against.

Is there anything else anywhere in column A below row 10?

 

[AndyGalloway]

No. From Row 10 down it is just a list of client activities related to the invoices, (Date, Month/Year, item, Qty, Invoice No., Invoice Cost, Payment In, Account Balance). I don't want the users to have to do anything except put another entry into the list and enter payments received (in column F). Honestly, I'm not looking for another way to do this. I just want CountA to do what it should or for someone to tell me where I am using CountA incorrectly.

 

[RoryA]

That's what I'm trying to get at. If you clear the entries below A10, select it, then press Ctrl+Down arrow, where does the cursor go to?

 

[AndyGalloway]

When I start a new sheet, select Cell A10 and press enter the cursor goes to Cell A11. If I use Tab, which is what I teach the users to do and I do myself, the cursor goes to B10. Either way, I don't get the right result for the CountA code.

 

[AndyGalloway]

Sorry, mis-read your question. Ctrl+Down arrow does to A158 where there was some spurious text

 

[RoryA]

I didn't say to use Enter or Tab. On the sheet that is causing the problem, with only A10 populated, select A10, then press Ctrl+Down arrow. What cell is selected?

 

[AndyGalloway]

That spurious text was in my template sheet and has reproduced into every client sheet. Thanks