Determine if an Excel column has any value?

Question

Question: with or without VBA, how can we determine if a specific column (say column "A") has any value in any cell in it?

Remarks: Question is about finding out whether a column (with a header, say, LastName) has any value (text/number) in it. The search is not on any specific value.

Reason for the Question:

1. We have more than a 1GB Excel file with about 1 million rows and several columns with headings. When we scroll down to one of the columns the column looks empty. But since the file has more than 1 million rows, we just keep scrolling down with no text found. But it is taking too much time to keep scrolling down to determine if there is any value inside the column. And, we may have to do the same for some other columns that do seem empty. So, we are looking for a better way to do it.
2. The issue is somewhat related to what's described in item 4 of this article: Tackling the most common errors when trying to import a CSV

Answer 1

Consider Conditional Formatting

Apply to Header Row only

Select header row cells containing headers (not the whole row)

Add CF Formula, and set format to suit your preference

=COUNTA(A:A)>1

Highlights headers in columns that contain data. To highlight columns headers that don't contain data, use

=COUNTA(A:A)<=1

Note: this will consider cells that contain an empty string (eg from a formula) to contain data. If you want to consider those cells as empty, use

=COUNTBLANK(A:A)=ROWS(A:A)-1