Excel VBA: Application defined or Object defined error

Question

I've written some code to look for sets of brackets in an excel file and white out the contents of the cells in between them. The code I have works for 26-27 lines before I get the error message.

Here is the code:

Sub macro()
Dim white As Long
Dim rowIndex As Long
Dim colIndex As Long
Dim lastRow As Long
Dim lastCol As Long

white = RGB(Red:=255, Green:=255, Blue:=255)

With ActiveSheet

lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For rowIndex = 1 To lastRow
        For colIndex = 1 To lastCol
            If .Cells(rowIndex, colIndex).Text = "[" Then
                colIndex = colIndex + 1
                Do While .Cells(rowIndex, colIndex).Value <> "]"
                    .Cells(rowIndex, colIndex).Font.Color = white
                    colIndex = colIndex + 1
                Loop
            End If
        Next colIndex
Next rowIndex
End With
End Sub

The error occurs on this line:

Do While Cells(rowIndex, colIndex).Value <> "]"

I tried adding in:

With ActiveSheet

Along with . before each Cell command but it did not make a difference. Any help is greatly appreciated.

Answer 1

If one of the cells containing [ or ] may have rogue leading trailing spaces/non-breaking spaces then a wildcard comparison should be made. Additionally, the worksheet's MATCH function can locate the bracketing cells with a wildcard search more efficiently than looping through each cell row-by-row.

Sub hide_cell_values()
    Dim whiteOut As String   '<~~ using alternate method .NumberFormat ;;;
    Dim rw As Long, n As Long, f As Long, l As Long

    whiteOut = ";;;"    'custom cell number format to show nothing in cell

    With ActiveSheet
        'process row by row in the .UsedRange
        With .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
            For rw = 1 To .Rows.Count
                ' check for existance of matching pairs
                If Not IsError(Application.Match("*[*", .Rows(rw), 0)) And _
                               Application.CountIf(.Rows(rw), "*[*") = _
                               Application.CountIf(.Rows(rw), "*]*") Then
                    ' [ and ] pairs exist and match in row.
                    f = 0: l = 0
                    For n = 1 To Application.CountIf(.Rows(rw), "*[*")
                        'this looks complicated but it just references the cells between [ & ]
                        f = Application.Match("*[*", .Rows(rw).Cells.Offset(0, l), 0) + l + 1
                        ' last safety check to ensure that [ comes before ]
                        If Not IsError(Application.Match("*]*", .Rows(rw).Cells.Offset(0, f), 0)) Then
                            l = Application.Match("*]*", .Rows(rw).Cells.Offset(0, f), 0) + f - 1
                            With .Range(.Cells(rw, f), .Cells(rw, l))
                                'this is a better method of not displaying text in a cell
                                .NumberFormat = whiteOut    '<~~ e.g. ;;;
                                'the old method of white-text-on-white-background (not reliable as .Interior.Color can change)
                                '.Font.Color = vbWhite
                            End With
                        End If
                    Next n
                Else
                    ' [ and ] pairs do not match or do not exist in row. do nothing.
                End If
            Next rw
        End With
    End With

End Sub

I have opted for a custom cell number format of ;;; rather than altering the font color to RGB(255, 255, 255) (see footnote ¹). A Range.NumberFormat property of three semi-colons in a row simply shows nothing; a white font's apparent visibility is subject to the cell's Range.Interior.Color property, the worksheet backgroun or even the 'Window background' in the computer's system settings.

    
    ^{Before running sub}

    
    ^{After running sub}

In the before and after images above, you can see that D2 retains its Range.Value property (visible in the formula bar) while showig nothing on the worksheet. Note: cell values can still be copied from a cell displaying nothing but that is a caveat of using the vbWhite method as well.

---

¹ _{There are predefined RGB long type constants for the basic VBA pallette. RGB(255, 255, 255) is equal to vbWhite. Full list available at Color Constants.}