CODEWITHSUNDEEP
excelvba
Derek
Derek

Reputation: 858

Excel 2010 VBA Length of Column given column index

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

Upvotes: 5

Views: 56716

Answers (7)

iDevlop
iDevlop

Reputation: 25272

dim i as integer
For i = 1 To Columns.Count
    debug.print i, cells(i, rows.count).end(xlup).row
Next

Upvotes: 0

Marco
Marco

Reputation: 57593

I think you could use

LastRowColA = Range("A" & Rows.Count).End(xlUp).Row

to find last used row in column A.
Navigating through every column you can have what you want...

Upvotes: 1

niko
niko

Reputation: 9393

len = sheet1.Range("B" & Rows.Count).End(xlUp).Row ' gives the count of B column
len = sheet1.Range("A" & Rows.Count).End(xlUp).Row ' gives the count of A column

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

This will count text non-empty cells in VBA:

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

Upvotes: 9

Jon Egerton
Jon Egerton

Reputation: 41569

You can use an alternative approach using the worksheet function CountA as follows

Test Data:

Col1 Col2 Col3
A    A    A
     B    B
C    C    C
D    D    D
E         E

VBA function:

Sub Test()

    Dim sht As Excel.Worksheet
    Set sht = Worksheets("Sheet1")

    Dim i As Integer
    Dim max As Integer
    Dim length As Integer
    max = 0
    For i = 1 To 3
        'CountA returns the number of non-blank cells in a range
        length = WorksheetFunction.CountA(sht.Columns(i))
        If length > max Then max = length
    Next i

MsgBox max - 1 ('remove 1 if you have column headers)

End Sub

Upvotes: 0

brettdj
brettdj

Reputation: 55692

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

Upvotes: 6

Charles Williams
Charles Williams

Reputation: 23550

You can find the last non-empty row using Range(large row number, column).End(xlUP).row where large row number should be large enough to always be beyond your last used cell.

Upvotes: 0

Related Questions