0

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

excel table

Amanda S
  • 103
  • 1
  • 9

1 Answers1

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")

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • When I am saving all macros in my Personal Macro WorkBook, I guess it's not necessarily to fully qualify the ranges, am I right? – Amanda S Apr 29 '14 at 18:25
  • I would say it makes it more necessary, but instead of `ThisWorkbook` use `ActiveWorkbook` and instead of specifying a sheet, use `ActiveSheet` – tigeravatar Apr 29 '14 at 18:27
  • @AmandaS, you're right, if you're going to use this macro always in _active wroksheet_, just leave it as it is now, `Set rng1 = Range("A2:A" & lastrow)` – Dmitry Pavliv Apr 29 '14 at 18:34