Search code examples
vbscripthiddenfield

vbscript : To Search a word only from the visible cells in an excel

Using vbscript, how to find/search a word in an excel only from the visible cells. i.e if a word is present in any of the hidden rows or columns, it should not return that.

Currently, i am using as below, but it returns the text irrespective of visibility. How to retrict visiblity for this?

Set foundText = excelFile.worksheets(i).Range("A1:H500").Find("Hello")

Solution

  • You can use the xlCellTypeVisible qualifier to filter only visible cells. Here's how:

    Const xlCellTypeVisible = 12

Dim r
Set r = excelFile.Worksheets(i).Range("A1:H500").SpecialCells(xlCellTypeVisible).Find("Hello")

If r Is Nothing Then
    MsgBox "Text not found in a visible cell."
Else
    MsgBox "Text found in a visible cell."
End If