Forum Discussion

jukhamil's avatar
jukhamil
Brass Contributor
Sep 17, 2021

How to count the number of blank cells in a column?

I would like to count the number of blank cells in a column.

 

I am trying to do this via:

 

Set ws = Worksheets("Sheet1")

Set col = ws.Columns(1)

Application.WorksheetFunction.CountA(col)

 

This returns 888 for every column I have tried it on, whether or not there is lots of text-filled cells in the column or very few.

 

I think the issue might be that it is counting cells with empty strings in them as being non-empty.

 

Is there a count function which would consider a cell with an empty string in it as empty?

 

Thank you

Macros and VBA

6 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 17, 2021

    jukhamil 

    Do you really want to count the number of blank cells in an entire column? That will probably be very high.

    You could try

     

    Application.WorksheetFunction.CountBlank(col)

     

    or

     

    Application.WorksheetFunction.CountIf(col, "")

    • jukhamil's avatar
      jukhamil
      Brass Contributor
      Sep 17, 2021
      Thanks. Actually I did misspeak there. I would like to count the number of non-empty cells, not the number of empty or blank ones. What I actually would like to do is figure out which columns are completely empty and delete them. I found some examples online suggesting I use if CountA = 0.

      If you know a way to count cells that do not have empty strings in them or another way to achieve the above I'd love to hear about it.

      Thanks very much.

Resources