# Count number of non-blank cells in a range

1. ## Count number of non-blank cells in a range

I have a file........see enclosed.

I want to count the number of non-blank cells in a range from A to Z along a given row.

Then once the loop is done i want it to tell me the row which had the most non-empty cells but its not working.

2. ## Re: Count number of non-blank cells in a range

Hi welchs101, how you display this is up to you but the following procedure will count everything (even the headers).
``Please Login or Register  to view this content.``

3. ## Re: Count number of non-blank cells in a range

FYI - you can use WorksheetFunction.CountA() to count the number of non-empty cells in a given range.

4. ## Re: Count number of non-blank cells in a range

If you want a count of both empty and non empty cells, try:
``Please Login or Register  to view this content.``

5. ## Re: Count number of non-blank cells in a range

Originally Posted by Colin Legg
FYI - you can use WorksheetFunction.CountA() to count the number of non-empty cells in a given range.
Do you apply that to the whole CurrentRegion at once?

6. ## Re: Count number of non-blank cells in a range

Wow that shortens the code!
``Please Login or Register  to view this content.``
Nice one Colin!

7. ## Re: Count number of non-blank cells in a range

hi Mordred,

But i was trying to count the number of non-blank cells in a given range and that range was a row from about A to Z. I then wanted to know which row had the most non-blank entries. Your code, while very very cool (and i can use it in another place), counts the number of non-blank cells in the "entire" range of a current region where c5 is in that region. I am not guaranteed that c5 is in the region of interest.

8. ## Re: Count number of non-blank cells in a range

Hi welchs101, The following code works base on you selecting a whole row and then running the code, which is also based on the workbook you provided where the first fill column is at C.
``Please Login or Register  to view this content.``

9. ## Re: Count number of non-blank cells in a range

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1