Excel.. CountIf? 1

[HobbitK]

HI everyone..
I have a VB Project that fills in a spreadsheet. I need to get the count of a the number of cells in a given range
G1:G62 that are not empty. the data put in there is ALWAYS a string. Using the CountIF() function in Excel I can define the Range but do not know how to tell it "Count it if it contains any letter". or is there a better function to use for this?
Also, since the range of cells will never be more than what G1:G62, would it be a good idea for me to Name that Range "CountIt"? I think it may be easier for further programming, but I have VERY limited knowledge in programming to Excel.
Thanks
Michael

 

[HobbitK]

One more question on this...
How do I remove a name from a range? I tried selecting the name and clearing, deleting, looked in Excel Help.. I need the data in the cells but not the name. I named an entire Row and only need certain cells in it to be named.

 

[Zathras]

You can use an "array formula." This is a special form of a formula which is entered by pressing CTRL+SHIFT+ENTER instead of simply pressing enter. The result appears in the formula window with a pair of curly braces surrounding the formula. (But you don't type the curly braces, Excel puts them there.)

If you type

   =SUM(IF(ISTEXT(G1:G62),1,0))

and press CTRL+SHIFT+ENTER
you should see the results you want. Note that cells with spaces are counted, but numbers and completely blank cells are not.

You can delete a range name by selecting (from the menu) Insert/Name/Define... then select the range name from the list and click Delete.

Generally, yes, it is a very good practice to use range names instead of "hard-coded" cell references. You will see examples of code here without using range names because it is generally easier to see in simple cases what is happening. But for a production worksheet, range names are definitely the way to go. So, unless you have a specific situation (which would be rare), you should use range names.

 

[Dreamboat]

Why not just:

CountA(g1:g62)?
Anne Troy

http://www.TheOfficeExperts.com

 

[HobbitK]

Zathras,
I tried your idea and Excel kept wanting to change the Formula... It kinda growled at me when i told it NO I dont want to accept its' suggestions So after closing several message boxes, I ended up with a #Value in the cell.
I am willing to bet it is something I have done or not done properly.


Anne ..
Once more ..
Thank-you ma'am.

 

[Dreamboat]

Sure thing. Anne Troy

http://www.TheOfficeExperts.com

 

[Zathras]

Sorry you couldn't get my formula to work. I always test before posting and it works fine for me on Excel 2K. (I just tested it again by copying from my post and pasting into Excel and it still works.)

As for COUNTA, that is certainly the easiest way as long as you don't care whether the cells being counted have text or numbers.

However, your original post stated "Count it if it contains any letter" and so I took that literally and provided the solution that counts only cells with letters and not cells with only numbers.

 

[HobbitK]

Zathras ...
I will keep the code you provided and I am sure it will come in handy. As I said in earlier post, I had ALL macros disabled and did not know it. Since I fixed that, I have not tried yours, but I am sure it will work.
Thanks
Michael