Countif with selection to the last non-empty cell in a column

Question

My rng1 only contains non-empty cells to the last cell in column A. I want to count cells greater than the value in B1 in rng1. However, this formula didn't work and showed error was due to empty cells.

Sub CountRng1()
Dim rng1 As Range
Set rng1 = Range(Range("A2"), Range("A2").End(xlDown))
Range("C1").formula="=countif(rng1,"">""&B1)"
End Sub

Answer 1

Using xlDown highly unreliable. Use this one instead:

Sub CountRng1()
    Dim lastrow As Long
    Dim rng1 As Range

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng1 = Range("A2:A" & lastrow)
    Range("C1").Formula = "=COUNTIF(" & rng1.Address & ","">"" & B1)"
End Sub

Read this please: How to determine last used row/column

Also I suggets you to fully qualify your ranges, i.e. change Range("A1") to ThisWorkbook.Worksheets("sheetName").Range("A1")