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

Question

Hi mates,

I'm just starting with "programming" VBA on Excel to automatize some anoying manual stuff...so this will probebly sound like a total nooby question, although I'm not very familiar to VBA Objects and so on.

So my quesstion: Is there a more elegant way to find out the length of a column? 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?

Greetz, Poeschlorn

Answer 1

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

Answer 2

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