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?

brettdj brettdj · Accepted Answer · 2012-06-07T00:43:58

Several options depending on whether you have blanks inside your range (use method 1), or more simply if you want to find the last used cell (use method 3)

These options using column A of the activesheet as an example

1. SpecialCells

If the empty cells are truly empty then you can use SpecialCells to work with the formulae cells (that start with =) and/or constant cells

   Sub GetNonEmtpy()
    Dim rng1 As Range
    Dim rng2 As Range
    On Error Resume Next
    Set rng1 = Columns("A").SpecialCells(xlConstants)
    Set rng2 = Columns("A").SpecialCells(xlFormulas)
    On Error GoTo 0
    If Not rng1 Is Nothing Then MsgBox "Constants in " & rng1.Address(0, 0)
    If Not rng2 Is Nothing Then MsgBox "formula in " & rng2.Address(0, 0)
    'then work with these ranges
End Sub

2. Last cell look up

Sub LastCellLookup()
    Dim rng1 As Range
    Set rng1 = Cells(Rows.Count, "A").End(xlUp)
    If rng1.Row <> 1 Then
        MsgBox "last cell is " & rng1.Address(0, 0)
    Else
    'check first cell is not empty
        If Len(rng1.Value) > 0 Then
            MsgBox "last cell is " & rng1.Address(0, 0)
        Else
            MsgBox "row is blank"
        End If
    End If
End Sub

3. Find

Sub LastCellFind()
    Dim rng1 As Range
    Set rng1 = Columns("A").Find("*", [a1], xlValues, , xlByRows, xlPrevious)
    If Not rng1 Is Nothing Then MsgBox "Last cell is " & rng1.Address(0, 0)
End Sub

Skip empty rows in Excel column

2 Answers