Testing a range for blanks

[Guest]

I have a spreadsheet that has 5000+ rows. Some of the rows have cells 3-10
as blanks. I need to report when these cells are blank. Is there a simple
way to test the range for blanks or do I need to cycle through each cells and
evaluate them?

Thanks in advance.

Guy Normandeau

 

[Norman Jones]

Hi Guy,

Try something like:

'=============>>
Public Sub Tester()
Dim rng As Range
Dim rng2 As Range

Set rng = Range("C1:J5000")

On Error Resume Next
Set rng2 = rng.SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng2 Is Nothing Then
'do something, e.g.:
rng2.Cells.Interior.ColorIndex = 6
End If
End Sub
'<<=============