Using COUNTA in my summary Sheet

[jgriff26]

In this macro (original macro here)my workbook is consolidated into a summary sheet. Within that summary sheet I would like the last column to return the number of rows -1 in the corresponding worksheet.I highlighted the part of the code that I need help with. I understand It should be WorksheetFunction.CountA but I am not sure of how to refer the range. Each summary row returned in the summary sheet has a corresponding tab.

Sub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook


    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    'Delete the sheet "Summary-Sheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Summary").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True


    'Add a worksheet with the name "Summary-Sheet"
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = "Summary"
    Newsh.Range("A1:E1").Value = Array("EditNumber", "EditDescription", "IDExample", "FailedValue", "NumberofFailures")
    Range("A1:E1").Interior.Color = RGB(192, 192, 192)
    Range("A1:E1").Font.Bold = True
    RwNum = 1
    'Formatting Headers
    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then
            ColNum = 1
            RwNum = RwNum + 1
            'Create a link to the sheet in the A column
    Newsh.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", _
                         SubAddress:="'" & Sh.Name & "'!A1", TextToDisplay:=Sh.Name


           [COLOR=#ff0000] For Each myCell In Sh.Range("C2,D2,F2,CountRow") [/COLOR] 'Range of cells to be returned to Summary Sheet
                ColNum = ColNum + 1
                Newsh.Cells(RwNum, ColNum).Formula = _
                "='" & Sh.Name & "'!" & myCell.Address(False, False)
            Next myCell


        End If
    
    Next Sh


    Newsh.UsedRange.Columns.AutoFit


    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub