Auto Hide/Unhide all rows based on value in colum

I have "sheet2" in which data populates dependent upon what's entered into "sheet1". The data populated is either "N/A" or a particular statement.

In sheet2 I'd like any rows with "N/A" in Column G to auto hide and become unhidden if data in sheet1 is changed resulting in a change from "N/A".

I know VBA is probably my answer, but I have no knowledge/experience working with those.

user932858

Posted 2018-08-11T18:54:10.013

Reputation: 1

1Since you already an excel sheet with formulas.. mind sharing a sample file or data+formula in Sheet1&2 for a clearer view. || "Column G to auto hide and become unhidden" <--- IMHO select header > data > filter will work automatically without vba/formula.. but it only hides rows, not columns. – p._phidot_ – 2018-08-11T19:23:41.213

Can't share due to proprietary reasons, but I'll do my best to explain.

Sheet 1 "Heading" is a questionnaire. I have the check boxes linked to a cell to indicate TRUE or FALSE if selected or not. Sheet 2 pulls data from Sheet 3, "Narrative" depending on what is selected in Sheet 1 (TRUE or FALSE).

Example formula:

=IF(OR(Headings!B6=TRUE,Headings!B7=TRUE,Headings!B10=TRUE,Headings!B11=TRUE),Narrative!C29,"N/A")

N/A populates quite a bit, and in stead of a full row of "N/A's", I'd like those rows to hide until they show something else. – user932858 – 2018-08-11T19:35:27.177

So this is the formula contained in column G Sheet2.. right? – p._phidot_ – 2018-08-11T19:37:20.837

Yes. I figured I'd have to make a new Column G and put a formula like =h1 (h is old columng ) so it shows N/A or whatever and use that in the VBA. – user932858 – 2018-08-11T19:40:33.870

Answers

Since you want Excel to automatically Hide & Unhide Row/Column if designated column contains #NA error/Value. So in that case VBA (Macro) is the best and handy solution.

Enter this code as standard module in Sheet 2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim rng As Range
    Dim cl As Range

    Application.ScreenUpdating = False
    Set rng = [H2:H25]
    For Each cl In rng
        If IsError(cl) Then
            cl.EntireColumn.Hidden = cl = CVErr(xlErrNA)
        Else
            cl.EntireColumn.Hidden = cl = ""
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Note:

Adjust cell references in code as needed.
My suggestion is instead of hide/unhide Row better handle the formula Column only.
To hide Row replace,cl.EntireColumn.Hidden with cl.EntireRow.Hidden.

Rajesh S

Posted 2018-08-11T18:54:10.013

Reputation: 6 800

Try this .. put in formulas..

H1 --> =IF(G1="N/A",0,1)
H2 --> =IF(G2="N/A",H1,H1+1)
J1 --> =IFERROR(INDEX(G:G,MATCH(I1,H:H,0)),"")

rag H2 + J1 downwards. some running numbers..

I1 --> 1
I2 --> 2
I3 --> 3
I4 --> 4 and so on..

Hope Column J is what you're looking for.. (:

p._phidot_

Posted 2018-08-11T18:54:10.013

Reputation: 948

Forgive my ignorance.. what does this do for me? – user932858 – 2018-08-11T20:32:36.923

"I'd like those "N/A" rows to hide " <-- You'll get all the column G Narrative!C29 values, skipping the "N/A" rows. || Btw, I'm reproducing this without any sample data.. just the formula you shared.. I'm also assuming that there is no header in your data (1st data is located @ G1, second @ G2, no column title..) . Without sample data/screenshot, all of us here can best work with assumptions of the data structure/format. ( : – p._phidot_ – 2018-08-11T20:45:36.067

Another way of looking at this.. I don't have a one-line formula or vba script to "remove" the "N/A" rows. I need 3 columns (2 helper + 1 result) to achieve this feat. If you get stuck at understanding the steps/formula.. just buzz it here.. ( : || IMHO I'm not the best (skill) person here, but I work my way of using excel without vba or array formulas.. and here I am, with the best shot I've got. /(^_^)\ – p._phidot_ – 2018-08-11T21:22:35.260

I just tried this in my spreadsheet. It does get rid of the "N/A", but it doesn't hide the rows. Just creates a blank space. – user932858 – 2018-08-12T12:53:12.450

Yup, It doesn't. You'll need vba if you want to do it in the same sheet. || My workaround is .. to 'load' column A to F data to column K to P using =IFERROR(INDEX(A:A,MATCH(I1,$H:$H,0)),""). Then just 'map' the result in column J to P in an "IntendedOutput" sheet.. I'll be as if the 'skipped' row was never there.. here is my sample file.

– p._phidot_ – 2018-08-13T07:48:11.633