Hide rows based on value in a different worksheet

[RICH1980]

Hi

I've been looking through the threads and cannot seem to find a similar thread.

I have a workbook with where I have two sheets. ("Data" and "Report")

The 'data' sheet has multiple options and drop down which trigger cells in the 'report' sheet to either have a sentence or a tigger word "hide".

Basically - I want all the cells with 'hide; in to hide (or row to hide) and everything else to show.. ideally with ae-size function.

I've been trying to adapt this - butwith no joy in ('data')


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim CheckNum As Long
Application.ScreenUpdating = False
with sheets("report")
If Not Intersect(Target, Range("e2:e259")) Is Nothing Then
For CheckNum = 259 To 1 Step -1
If Cells(CheckNum, 5).Value = "hide" Then

Cells(CheckNum, 5).EntireRow.Hidden = True
Else: Cells(CheckNum, 5).EntireRow.Hidden = False
Cells(CheckNum, 5).Rows.EntireRow.AutoFit
End If
Next CheckNum

End If

Application.ScreenUpdating = True



End Sub


any help would be great

 

[Joe4]

The 'data' sheet has multiple options and drop down which trigger cells in the 'report' sheet to either have a sentence or a tigger word "hide".

Please explain this structure a bit more. What cells are these drop-downs in, and what are the possible values?
Which values should "trigger" an action?

Basically - I want all the cells with 'hide; in to hide (or row to hide) and everything else to show.

What range on your "Report" sheet has these "hide" values?

 

[RICH1980]

Hi Joe, thank you for your reply.

In the 'data' sheet there are options to select, which completes a table, which have 5 options - option 1 - 5 across the top. The column (a) then has the specifics about their requirements - colour - size - brand - model - new / old - budget.

I have then used a 'concentate' function to develop sentences (apologies this is on a third sheet 'sentences' ) - based on the selection on the 'data' sheet - so "you have selected a 'green' machine", " you have selected a 'new' machine" etc..... however, there are some free format sentences that are also added depending on the selction made. such as - New machines from 'brand' are gaurenteed or "x" years.

On the 'report' sheet in column "e" have a column of formulas that are variable -e.g "if(data'$d$10=new,a1,"hide") - so if "new" is selected on 'data' the required sentences are visable in the cells (column E) of the 'report' sheet - but if not the cell says "hide". I have these types of formula from E5:E70 of the 'report' sheet.

In 'data' i have a button that generates a report from the 'report' sheet on 'visable' rows of column 'E' of the 'report' sheet - so i need to hide the rows / cells that contain "hide" - in the 'report' sheet from the 'data' sheet, either as a separate button or as part of the 'generate report' button on the 'data' sheet.

IE -the user would not see the 'report' sheet or the 'sentences' sheet.

If this makes more sense.

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each cell In Sheets("report").Range("E2:E259")
        If cell.Value = "hide" Then
            cell.EntireRow.Hidden = True
        Else
            cell.EntireRow.Hidden = False
            cell.Rows.EntireRow.AutoFit
        End If
    Next cell

    Application.ScreenUpdating = True

End Sub

 

[RICH1980]

Hi, sorry for not replying sooner, have not been able to try the code until now and its works well and does the job. Thank you so much

 

[Joe4]

You are welcome.