Detect if range is empty and return index of non-empty rows

Question

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:

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.

Answer 1

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

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

Answer 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

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