CODEWITHSUNDEEP
vbaexcel
H Foucault
H Foucault

Reputation: 57

Excel finding blanks cells

I have tried using the GUI and VBA to find and fill in blanks.

Unfortunately, both do not find the last set of blanks.

Example:

Example

If I highlight cells 1 through 20, and use the GUI ( Find -> Goto -> Blanks....) it only highlights items above the done line. Similar with this code 

With Range("B1:B20").SpecialCells(xlCellTypeBlanks)

If I highlight from 17 to 20, and use the GUI, it does find/highlight the last blank cells. The cells do not contain data ( I have checked ). What can I get to get Excel to not stop at line 17

Thanks

Upvotes: 0

Views: 76

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37377

Try this simple code:

Sub HighlightBlanks1()
    Dim c
    For Each c In Range("B1:B20")
        If IsEmpty(c) Then
            c.Interior.ColorIndex = 3
        End If
    Next
End Sub
'or using your approach
Sub HighlightBlanks2()
    Range("B1:B20").SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 3
End Sub

Upvotes: 1

Related Questions