TIA for any help offered!
I have a macro in which I am looking to see if a range is blank (you may have seen it in my other posts to this site). In the macro, I have tried the following arguements (I have tested all rng(s) and variables in the macro and know they work):
For Each cell In rng1
If IsEmpty(cell) Then
Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, icol).Offset(0, -2))
nonblank = 0
With rng3
On Error Resume Next
nonblank = .SpecialCells(xlCellTypeFormulas).Cells.Count
nonblank = .SpecialCells(xlCellTypeConstants).Cells.Count
End With
If nonblank > 0 Then
cell.Interior.ColorIndex = 6
End If
End If
Next cell
and I have tried...
For Each cell In rng1
If IsEmpty(cell) Then
Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, icol).Offset(0, -2))
nonblank = 0
With rng3
On Error Resume Next
nonblank = Evaluate("CountA(rng3)")
End With
If nonblank <> 0 Then
cell.Interior.ColorIndex = 6
End If
End If
Next cell
Both of these statements seem to work only some of the time. There are instances where rng3 holds no data but the macro calculates that data is present. I have checked the cells in question using the functions COUNTA and ISBLANK, both of which indicate the cell is blank.
Does it have something to do with the statements I used?
Bookmarks