CODEWITHSUNDEEP
vbaexcelexcel-2007
Jörg T.
Jörg T.

Reputation: 25

Hide Rows when a specific string is in a cell

I have a problem with VBA in Excel 2007. I need a macro to hide rows when a specific string is in a cell. (e.g. "System PPP cancelled")

My macro:

Sub HideRows()  
    Dim Cell As Range  
    If InStr(Cell, "cancelled") And Rows(Cell.Row).Hidden = False _  
    Then Rows(Cell.Row).Hidden = True  
    Next Cell  
End Sub

Unfortunately, I get the runtime-error '13'... Can you help me?

Upvotes: 0

Views: 2514

Answers (1)

mainguy
mainguy

Reputation: 8331

This works in Excel 2010:

Sub hide_cancelled()
For i = 1 To Rows.Count
If InStr(Cells(i, 2).value, "cancelled") And Rows(i).Hidden = False Then
    Rows(i).Hidden = True
End If
Next i
End Sub

Note that this will iterate over ALL rows in the spreadsheet, no matter if they contain any data or completely empty. This may take a while! So instead of Rows.Count you should enter a more sane value.

Also this will expect the search value in column 2. Change this in Cells(i,xxx)

Upvotes: 1

Related Questions