CODEWITHSUNDEEP
vba
Robin
Robin

Reputation: 521

VBA loop through range and output if complete range is empty

I have searched a lot about my question but could not find the answer I need.

I have a table A1:DT97138. Within this table I want to check per row, starting from cell B2 to DT2 if all the cells in one row are empty. Then output "Empty" or "Not Empty" in the next cell, DU2. Then do the same for row 3, 4 etc to 97138 (and output the same results row per row in DU2, DU3 etc).

I found out how to do this for 1 specific row, as you can see below, but I cannot find out how to iterate trough the whole range, row by row.

Sub rowEmpty()

    Dim rng As Range, r As Range
    Set rng = Range("B2:DT97138")

    If WorksheetFunction.CountA(Range("B2:DT2")) = 0 Then
        Cells(2, 125) = "Empty"
    Else
        Cells(2, 125) = "Not Empty"
    End If

End Sub

Thanks for your help!

Upvotes: 0

Views: 78

Answers (2)

iDevlop
iDevlop

Reputation: 25252

Enter your formula at once in the last column: 

With Range("DU2:DU97138")
     .Formula = "=IF(COUNTA(B2:DT2)=0,""Empty"",""Not Empty"")"
     'then eventually convert it to constants
     .Value = .Value
End With

No loops, simpler, probably much faster :-)

Upvotes: 1

newacc2240
newacc2240

Reputation: 1425

Your are doing well. Just need to loop thru the range like this.

Sub rowEmpty()
    Dim rng As Range, r As Range
    Set rng = Range("B2:DT97138")

    For Each r In rng.Rows
        If WorksheetFunction.CountA(r) = 0 Then
            Cells(r.Row, 125) = "Empty"
        Else
            Cells(r.Row, 125) = "Not Empty"
        End If
    Next r
End Sub

Upvotes: 2

Related Questions