Countif a active range and not a predefined range

Question

I am trying to find and return the number of times the word “overflow” appears in an active range in Excel.How do I search in the active selected range and not a predefined range like A1:A50

Dim MyCount As Long
MyCount = Application.Countif(Range("A1:A50"),"Oveflow")
MsgBox "I have completed my search and found  " & MyCount & " cell(s)."

Is it possible to perform countif on active selected range ?

do you wanna count as 2 if one cell have repeated overflow for twice ? if yes, countif may not working well. — Eric K., Dec 07 '15 at 10:29

score 2 · Answer 1 · edited May 23 '17 at 12:04

The Selection property is the cell or range of cells in the current selection.

MyCount = Application.Countif(Selection,"Oveflow")

That will return the count from the currently selected cells containing Oveflow^[sic].

_{More examples of using the Application.Selection property (not Select) can be found in How to avoid using Select in Excel VBA macros}.

score 2 · Accepted Answer · answered Dec 07 '15 at 10:19

2

You can simply use Selection in VBA to reference the range that you actually have selected :

Dim MyCount As Long
MyCount = Application.Countif(Selection,"Oveflow")
MsgBox "I have completed my search and found  " & MyCount & " cell(s)."

answered Dec 07 '15 at 10:19

R3uK

14,417
7
43
77

Countif a active range and not a predefined range

2 Answers2