Hiding Rows Based on a Cell Value

If I have a value in a merged cell, how do I get it to then hide all the rows affected by the merged cells if they meet a criteria

Hi all, this is a great little macro (especially for someone with limited experience. My use case is a bit different so I'm hoping I can get some help here. This macro works if all the rows you want to hide are one after the other. In my spreadsheet I have several groupings of rows that are not one after another. I need to identify several "ranges" or rows and then hide them based on a cell value for each row. Similar to this macro but for several groups of rows.

Additionally, I want to hide a few text "header" rows if the rows under that group are completely hidden. No need to show the header row if there's no content showing (because it was hidden with the macro described above). Can someone offer some suggestions? Thanks!

None of the variables in this tip's macros are declared which is bad practice.

Instead of defining the start row, end row and check column in the macro you can better simply select the rows in the check column and run this tiny macro:

Sub HURows()
Dim cell As Range
For Each cell In Selection
'change <> 4 to suit you need, i.e. < 5 as in this tip
cell.EntireRow.Hidden = cell <> 4
Next cell
End Sub

Nevermind! I figured it out.

What if the row start and end change if a user inserts or deletes a row above where the code is to begin. Can you name a row to make the code work? How would the code change?

Hi Vanessa
Most likely it can be done
In VBA, those sorts of things can usually be done in many different ways .
( But it’s a bit like asking if you can cut up a length of string. If you wanted a macro to do what you want, then you would need to be more specific about exactly what you want to do, which rows, which columns , how the macro should start, automatically on a cell entry or by you choosing to run it … etc….. etc… )
Alan ( Elston )

can you get excel to look at a row and only hide that row if all of the rows for particular columns are blank?

Hello Erica .. .. I have a solution and macros for you .. .. but it is a bit long to have pasted in Allen Wyatt’s comments section .. .. here is a link to a pair of spare forum post that I put it in .. .. https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)-Event-Coding?p=13748&viewfull=1#post13748 .. .. https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)-Event-Coding?p=13749&viewfull=1#post13749 .. … :: ( If you have problems getting at those links , then post your question again over at that forum ( excelfox.com ) , and someone will set you straight ) .. ... Alan Elston

This is my script:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 And Target.Row = 5 Then
If Target.Value = "Yes" Then
Application.Sheets("FAR").Select
Application.Rows("14").Select
Application.Selection.EntireRow.Hidden = False
Application.Sheets("Additions").Select
Application.Rows("6").Select
Application.Selection.EntireRow.Hidden = False
ElseIf Target.Value = "No" Then
Application.Sheets("FAR").Select
Application.Rows("14").Select
Application.Selection.EntireRow.Hidden = True
Application.Sheets("Additions").Select
Application.Rows("6").Select
Application.Selection.EntireRow.Hidden = True
End If
End If
End Sub

How do I make this continue for the next 19 rows?

The idea is to:
hide row 14 in Tab FAR and Row 7 in Tab Additions if the value in row 6 Tab Additions is "NO" and leave unhidden if yes
hide row 15 in Tab FAR and Row 8 in Tab Additions if the value in row 7 Tab Additions is "NO" and leave unhidden if yes
hide row 16 in Tab FAR and Row 9 in Tab Additions if the value in row 8 Tab Additions is "NO" and leave unhidden if yes

and so fort 20 times

Please can you help me with the reloop of this script

Thank you very much for your formulation.

Hello there,
this is very helpful, I need to write a macro that hide the entire row if the cell in a designated column is red or beje. If the cell is not colored, I need the marco to take the value inside another column and copy it in that cell.

so basically, The column to analyze with the colors is Q, the macro needs to check if the cells in Q have these two specific color, if so, then the entire row needs to be hidden. If the cells in Q does not contain these two colors, I need the macro to copy the value in the same cell number but from row C into the Q cell.

I'm very new to VBA, would that be possible? I know the color index is 19 and 3.

Hi Karen
This is a good start point for Event type coding
https://www.youtube.com/watch?v=0EXdPcbsTZI

Thanks Alan,
I did see that but didn't know if it applied, as I have not gotten very accustom to custom macro writing. I will research it. Thank you for letting me know this is the right track.

@Karen
Have a look at my answer to Ryanne a few comments down. That may give you some ideas.

Alan Elston

Hi Alan,

I appreciate what you have provided here. I have created a customer form in excel that has a number sections. Some sections only apply to certain customers, based on options they choose from a validated list. With this bit of code, I can envision using an IF statement to populate a hidden cell with a number that would in turn allow an entire row hide (or unhide depending on the circumstance). My question is this, how do I make this code trigger on its own while the customer fills out unlocked fields on the excel "form" I have made?

Thanks again!!! Can't wait to get your advice!

best code I found online so far to hide row based on cell value in a particular column.
I modified it to hide the row based on the value in the 24th column that states NO DATA.

Sub HideRows()
BeginRow = 1
EndRow = 1600
ChkCol = 24

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "NO DATA" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

Sub UnHideRows()
BeginRow = 1
EndRow = 1600
ChkCol = 24

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "NO DATA" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Hello Ryanne

Rather than modifying the coding, it would probably be easier to use a simple “events” type coding which automatically kicks in when a range value is changed in a worksheet. Something of this form:.



Private Sub Worksheet_Change(ByVal Target As Range)

Target.EntireRow.Hidden = True

End Sub



This coding will need to be in a worksheet code module:

excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11486&viewfull=1#post11486



Alan Elston

How would i modify the code to change automatically based on a change in cell value?

What do you have in your cells, Ryan?
( Allen Wyatt's coding des not error for me , even if i have text in some cells, as VBA will allow a < or > comparison of text with numbers )
Alan Elston

I've copy and pasted your second set of code directly into my sheet and get a type mismatch error.