Skip empty rows in Excel column

Question

In a scenario where the user selects a whole column in Excel and I must act on cells inside the column, how do I efficiently ignore rows that are empty. A whole column has over 1 million cells! Please help! The range comes from ``` var range = Application.ActiveWindow.RangeSelection; ``` Ultimately I want to do something using ``` for (int i = 0; i < range.Rows.Count; i++) ``` where Rows.Count should be the non-empty Row count... maybe there is a way of locating the last cell with something in it?

Accepted Answer

It sounds to me like you want a good idea on what the upper bound on the number of rows is so you don't end up looping all the way to the bottom of the workbook.

If that's the case, then you may be looking for `Worksheet.UsedRange` property, which contains the range of all cells that have ever been used (this includes cells where a value was entered and then deleted).

For example,

```
Dim MaxUsedRow As Integer
MaxUsedRows = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
MsgBox MaxUsedRows
```

will show the index of the last used row in entire workbook.

Skip empty rows in Excel column

Solution

Problem