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

User · Answer

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.

User · Answer

You may also use   UsedRange Rows Count

User · Answer

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

User · Answer

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

User · Answer

To find the last filled column use the following    lastColumn   ActiveSheet Cells 1  Columns Count  End xlToLeft  Column

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

Examples related to vba

Examples related to excel