Finding empty cells in a Row and doing something when found

[jimayers]

Hello - i would appreciate some help with finding empty cells in a row and then doing something when found. My code for testing is:

Sub TEST_Find_EmptyRows_DeleteRow()
Application.ScreenUpdating = False
Dim r as Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To Lastrow
    If Range(Cells(r, 1) & ":" & Cells(r, 26)) Is Empty Then Rows(r).Interior.ColorIndex = 5  '.Delete
   
    
Next r
Application.ScreenUpdating = True
End Sub

Thank you for all your help - Jim A

 

[JLGWhiz]

Try this

For r = 1 To Lastrow
    If Application.CountA(Range(Cells(r, 1) & ":" & Cells(r, 26)) = 0 Then Rows(r).Interior.ColorIndex = 5  '.Delete    
Next r

The CountA function will check for any data, including formulas that may exist in the target range.

The IsEmpty function in VBA is primarily used to determine if variables have been initialized, and not to test for data in cells. It will give you false readings if used to test cells for data content. You can test a single cell to equal an empty string ("") or if working with numerical data on a range of cells, you can use

If Application.Sum(<enter range here>) = 0

. And a couple of other ways to check if values are entered or not, but I would not use IsEmpty.

 

[jimayers]

Try this

For r = 1 To Lastrow
    If Application.CountA(Range(Cells(r, 1) & ":" & Cells(r, 26)) = 0 Then Rows(r).Interior.ColorIndex = 5  '.Delete    
Next r

The CountA function will check for any data, including formulas that may exist in the target range.

The IsEmpty function in VBA is primarily used to determine if variables have been initialized, and not to test for data in cells. It will give you false readings if used to test cells for data content. You can test a single cell to equal an empty string ("") or if working with numerical data on a range of cells, you can use . And a couple of other ways to check if values are entered or not, but I would not use IsEmpty.

Thanks for the details and help - extremely helpful.

Thanks - Jim A