Getting the number of filled cells in a column (VBA)

Question

My first column is an index, with monotonously increasing numbers, which ends at a non predictable point. Now I want to find out, how many entries this column has. Is there a better way than iterating throug this and watch out for an empty cell?

Answer 1

If you want to find the last populated cell in a particular column, the best method is:

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

This code uses the very last cell in the entire column (65536 for Excel 2003, 1048576 in later versions), and then find the first populated cell above it. This has the ability to ignore "breaks" in your data and find the true last row.

Answer 2

One way is to: (Assumes index column begins at A1)

MsgBox Range("A1").End(xlDown).Row

Which is looking for the 1st unoccupied cell downwards from A1 and showing you its ordinal row number.

You can select the next empty cell with:

Range("A1").End(xlDown).Offset(1, 0).Select

If you need the end of a dataset (including blanks), try: Range("A:A").SpecialCells(xlLastCell).Row

Answer 3

You can also use

Cells.CurrentRegion

to give you a range representing the bounds of your data on the current active sheet

Msdn says on the topic

Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only.

Then you can determine the column count via

Cells.CurrentRegion.Columns.Count

and the row count via

Cells.CurrentRegion.Rows.Count

Answer 4

You may also use:

UsedRange.Rows.Count

Answer 5

To find the last filled column use the following :

lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column