Selected Answer
Please try this code. It should be installed in a standard code module (none of the default modules Excel creates automatically but one which you ahve to insert, by default Module1 - rename to your preference).
Option Explicit
Sub InsertSUBTOTAL()
' 09 Aug 2018
Static Target As Range
Dim TargetAddress As String
Dim LastCell As Range
Do While Target Is Nothing
TargetAddress = InputBox("Enter the cell address in which to display the COUNTA subtotal:", , "E4")
If TargetAddress = "" Then Exit Sub
On Error Resume Next
Set Target = Range(TargetAddress)
If Err Or (Target.Cells.Count > 1) Then
MsgBox "Please enter a valid address of a single cell.", _
vbExclamation, "Invalid cell address"
Set Target = Nothing
End If
Loop
On Error GoTo 0
With Target
With .Worksheet
Set LastCell = .Cells(.Rows.Count, Target.Column).End(xlUp)
End With
TargetAddress = Range(.Offset(2), LastCell).Address
.Formula = "=SUBTOTAL(3," & TargetAddress & ")"
End With
End Sub
Note that the code counts entries in the column specified by InputBox from 2 rows below the specified cell to the last used cell in that same column, including the last cell itself. The code will remember the address entered once and will forget it when the workbook is closed. To further automate the action I suggest you add the code below to the code sheet of each worksheet on which you want to have the above action.
Private Sub Worksheet_Activate()
' 09 Aug 2018
InsertSUBTOTAL
End Sub
If there already is a procedure by the same name in that module, just add the call InsertSUBTOTAL (single, separate line) at the bottom of the existing procedure, above the existing End Sub. The effect of this addition is that the formula will be refreshed to include possible additions to or deletions from the count range whenever the sheet is activated. Since the code remembers the cell to refresh you wouldn't be aware of the updating.
EDIT 09 Aug 2018 =================================
The code below is a variation of the above which sets the subtotal in all worksheets in one go.
Option Explicit
Sub InsertSUBTOTAL()
' 09 Aug 2018
Dim Target As Range
Dim TargetAddress As String
Dim FirstCell As Range, LastCell As Range
Dim Ws As Worksheet
Do While Target Is Nothing
TargetAddress = InputBox("Enter the cell address in which to display the COUNTA subtotal:", , "E4")
If TargetAddress = "" Then Exit Sub
On Error Resume Next
Set Target = Range(TargetAddress)
If Err Or (Target.Cells.Count > 1) Then
MsgBox "Please enter a valid address of a single cell.", _
vbExclamation, "Invalid cell address"
Set Target = Nothing
End If
Loop
On Error GoTo 0
For Each Ws In ThisWorkbook.Worksheets
With Ws
Set FirstCell = .Range(TargetAddress).Offset(2)
Set LastCell = .Cells(.Rows.Count, Target.Column).End(xlUp)
.Range(TargetAddress).Formula = "=SUBTOTAL(3," & Range(FirstCell, LastCell).Address & ")"
End With
Next Ws
End Sub