Forum Discussion

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

Macros and VBA

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)

Application.WorksheetFunction.CountIf(col, "")

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.
- HansVogelaar
  MVP
  Sep 17, 2021
  jukhamil
  
  Try
  
  Application.WorksheetFunction.CountIf(col, "> ")
  - jukhamil
    Brass Contributor
    Sep 20, 2021
    Thanks. I can't find the official documentation for the "< " expression. Would it be possible for you to link to it? Thanks very much.
- Detlef_Lewin
  Silver Contributor
  Sep 17, 2021
  jukhamil
  Perhaps this helps:
  =(ROWS(column_range)-COUNTBLANK(column_range))=0