Delete row when linked cell values are empty

[cdfjdk]

I want my macro to loop through a series of files and, if all the cells B10:F10 in Sheet2 are empty, then delete row 10.


I think my problem is that the value in each cell is linked from Sheet1 and the zero for an empty field is hidden (e.g. ='Sheet1'!B22 & ""); I have tried to delete for both Cell.Value = " " and "0" without success.


The loop runs OK. If I set Cell.Value = "", then it deletes row 10 regardless of whether the fields are empty or not.

Guidance very gratefully received!

Dim found As Boolean


found = False


    Sheets("Sheet2").Select


        For Each cell In Range("B10:F10").Cells
            If cell.Value = " " Then
                found = True
            End If


        Next
        
        If found = True Then
        Rows(10).EntireRow.Delete Shift:=xlUp
        
        End If

 

[JLGWhiz]

You can try it this way.

Sub t()
Dim c As Range
With Sheets(2)
    For Each c In .Range("B10:F10")
        If c.Value <> "" And c.Value <> 0 Then
            Exit Sub
        End If
    Next
    .Rows(10).Delete
End With
End Sub

this will test for the value of Empty String or blank. Even if you have a formula that returns the "" value, it will not regard that cell as having value..

 

[cdfjdk]

Thank you, JLGWhiz - that is very elegant!
I have made one change, because the

Exit Sub

terminates the loop at the first file in which there is a value in the range. I have replace the

Exit Sub

with a

GoTo

that goes to the conclusion of the macro.

 

[JLGWhiz]

Thank you, JLGWhiz - that is very elegant!
I have made one change, because the

Exit Sub

terminates the loop at the first file in which there is a value in the range. I have replace the

Exit Sub

with a

GoTo

that goes to the conclusion of the macro.

Thanks for the feedback,
regards, JLG