跳过Excel列中的空行

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!

在用户选择Excel中的整个列并必须对列中的单元格进行操作的场景中，如何有效地忽略空行。一整列有超过100万个细胞!请帮助!

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?

行。计数应该是非空行计数…也许有办法找到最后一个有东西的单元格?

2 个解决方案

#1

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).

如果是这样，那么您可能正在寻找工作表。UsedRange属性，它包含使用过的所有单元格的范围(包括输入值然后删除的单元格)。

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.

将显示整个工作簿中最后使用的行的索引。

#2

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)

取决于您的范围内是否有空格(使用方法1)，或者如果您想查找最后使用的单元格(使用方法3)，可以使用多个选项(使用方法3)

These options using column A of the activesheet as an example

这些选项使用activesheet的A列作为示例

1. SpecialCells

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

2。最后细胞查找

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

3所示。找到

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

#1