Hide Rows Without Specifying the Row Numbers

[megalamchops]

Is it possible to to hide rows based on a cell value of N/A or Not Specified, but without specifying exactly the row number? N/A will always be in the same column and I just want to hide the row N/A is on and the next 3 rows following it. I have a long spreadsheet that is generated so its not the same every time. I don't think it's a good idea to manually specify hundreds of rows to hide.

 

[megalamchops]

If it is the result of a LOOKUP formula, then that IS actually the result of error that Excel is returning, and not a text value.
That is, unless you actually have the values of N/A in your lookup table, and it is returning that value, and not the #N/A error value it returns when it does not find a match.
So which one is it (it makes a very big difference in how we have to do this)?

Sorry, this is the formula in that cell.

=VLOOKUP($O$1,Sheet1!$A:$CY,MATCH("arbitrary",master_spec[#Headers],0),FALSE)

 

[Rick Rothstein]

Give this macro a try...

Sub HideErrorRowPlusThreeMoreRows()
  Dim Ar As Range
  For Each Ar In Columns("J").SpecialCells(xlFormulas, xlErrors)
    Ar.Resize(3).Resize(4).EntireRow.Hidden = True
  Next
End Sub

 

[megalamchops]

Give this macro a try...

Sub HideErrorRowPlusThreeMoreRows()
  Dim Ar As Range
  For Each Ar In Columns("J").SpecialCells(xlFormulas, xlErrors)
    Ar.Resize(3).Resize(4).EntireRow.Hidden = True
  Next
End Sub

It seems like it works for the ones that are #N/A, but not the ones that are N/A (Correct me if I'm wrong, I don't think this is an error because I type this value in the lookup table).

 

[Rick Rothstein]

So you have a mix of #N/A and N/A??? I don't see the "N/A" in the formula that you posted Message #11... how is the N/A getting into the cells?

 

[Joe4]

So you have a mix of #N/A and N/A??? I don't see the "N/A" in the formula that you posted Message #11... how is the N/A getting into the cells?

I am guessing that the VLOOKUP is working, and there is the literal text "N/A" in the column that they are returning.

 

[megalamchops]

Some of the cells are #N/A because I'm still working on the sheet sorry if this wasn't clear. Eventually there won't be any #N/A in the worksheet.

I have a table that =VLOOKUP($O$1,Sheet1!$A:$CY,MATCH("arbitrary",master_spec[#Headers],0),FALSE) is looking at and that specific column it is returning the cells have a value of N/A (I typed N/A, it's not like an IFERROR statement return)


lookup table example

 

[Joe4]

Here is a not-so-elegant method that will work:

Sub HideRows()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
    For r = Cells(Rows.Count, "J").End(xlUp).Row To 1 Step -1
        If Application.WorksheetFunction.IsNA(Cells(r, "J")) Then
            Rows(r & ":" & r + 2).EntireRow.Hidden = True
        Else
            If (Cells(r, "J") = "N/A") Then
                Rows(r & ":" & r + 2).EntireRow.Hidden = True
            End If
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[megalamchops]

Thanks! - its working.

 

[Joe4]

You are welcome.
Glad we were able to help.