检测范围是否为空并返回非空行的索引

I would like to write a VBA code to check if a Range in Excel is empty and, if not, what are the rows which are non-empty. This question 10811121 answers how to check if Range("A1:L1000") is empty:

我想编写一个VBA代码来检查Excel中的Range是否为空,如果不是,那么哪些行是非空的。这个问题10811121回答了如何检查Range(“A1:L1000”)是否为空:

WorksheetFunction.CountA(Range("A1:L1000"))

If the Range is not empty I would like to determine the indexes of the non-empty rows. It doesn't not matter how many cells are non-empty in the row. One solution is to check one by one if each row in the range is empty or not but I would like to know if there is an easier solution without looping.

如果Range不为空,我想确定非空行的索引。行中有多少个单元格是非空的并不重要。一种解决方案是逐个检查范围中的每一行是否为空,但我想知道是否有更简单的解决方案而没有循环。

3 个解决方案

#1

Use the Range.SpecialCells method to quickly find all non-formula, populated values in your range.

使用Range.SpecialCells方法快速查找范围内的所有非公式,填充值。

set rng = Range("A1:L1000").SpecialCells(xlCellTypeConstant)

#2

I would loop. The code is simple:

我会循环。代码很简单:

Sub dural()
   Dim i As Long, msg As String

   For i = 1 To 1000
      If Application.WorksheetFunction.CountA(Range("A" & i & ":L" & i)) > 0 Then
         msg = msg & "," & i
      End If
   Next i

   MsgBox msg
End Sub

#3

If you really have a problem with looping:

如果你真的遇到循环问题:

Dim vResult

Dim sFormula              As String

With Range("A1:L1000")
    sFormula = "IF(SUBTOTAL(3,OFFSET(" & .Address & ",ROW(" & .Address & ")-MIN(ROW(" & .Address & _
               ")),0,1))>0,ROW(" & .Address & "),""|"")"
End With
Debug.Print sFormula

vResult = Filter(Application.Transpose(Evaluate(sFormula)), "|", False)

#1