Excel 2010 VBA Length of Column given column index

Question

I am finding this surprisingly hard to find an answer for, given its simple description. I hope this is not a duplicate, though it probably is because of its simplicity, and I simply cannot find the answer.

In VBA, I have hundreds of columns, and I'd like to know all their lengths. I know there are exactly "COLS" columns. I want something like this:

For i in COLS
   length = 'Some one line formula to find the length of column i
   'Some code that works with the value of length
Next i

By length I mean the number of non-empty cells... For my specific purposes there will be no blank cells in the columns, and all the cells in the column I wish to count will contain text (all the rest will be empty).

Help would be much appreciated on this seemingly simple matter!

Edit: I also want to make this dependent on the column index (which will be 'i' in the loop above). I won't always know the column letter...

Answer 1

This will count text non-empty cells in VBA:

For i = 1 To Columns.Count
    n = WorksheetFunction.CountA(Columns(i))
Next

Answer 2

It is best to use a routine that works automatically from the last row in all Excel versions.

Dim lngRow As Long
lngRow = Cells(Rows.Count, "A").End(xlUp).Row