Skip blank rows in Excel column

Question

In a scenario, when a user selects an entire column in Excel, and I have to act on the cells inside the column, how can I effectively ignore rows that are empty. The whole column has over 1 million cells! Please, help!

Range:

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 a non-empty number of rows ... maybe there is a way to find the last cell with something in it?

Answer 1

It seems to me that you need a good idea about what is the upper bound on the number of lines, so you do not end the loop until the end of the book.

In this case, you can look for the Worksheet.UsedRange property, which contains the range of all previously used cells (this includes the cells in which the value was entered and then deleted).

For instance,

 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 the entire book.

Answer 2

Several options depending on the presence of spaces within your range (use method 1) or more simply if you want to find the last used cell (use method 3)

These parameters using column A from the action table as an example

1. SpecialCells

If the empty cells are really empty, you can use SpecialCells to work with formula cells (which begin 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. Search for the last cell

 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