PDA

View Full Version : hide rows based on highlighted cell in column



Sandler
05-26-2016, 07:42 AM
I am looking for a code that will hide a row and the next row when it sees a blue highlight in a cell in a specific column.
Also, code that will reverse the rows hidden (it cant generally hide all rows because i have other rows hidden that should remain hidden)

For hypothetical purposes it will be column C and the highlight color code will be 123.

Thanks :)

blanchard306
05-26-2016, 10:10 AM
This should work to hide and unhide the color code 123 highlighted row and the row below.


Sub HideRows()
Dim Cell As Range,
cRange As Range,
LtRow As Long
LtRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Set cRange = Range("C1:C" & LtRow)
For Each Cell In cRange
If Cell.Interior.ColorIndex = 123 Then
Cell.EntireRow.Hidden = True
Cell.Offset(0,1).EntireRow.Hidden = True
End If
Next Cell
End Sub


Sub unHideRows()
Dim Cell As Range,
cRange As Range,
LtRow As Long
LtRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Set cRange = Range("C1:C" & LtRow)
For Each Cell In cRange
If Cell.Interior.ColorIndex = 123 Then
Cell.EntireRow.Hidden = False
Cell.Offset(0,1).EntireRow.Hidden = False
End If
Next Cell
End Sub

p45cal
05-26-2016, 10:30 AM
See attached with toggle button.

Sub blah()
'Application.ScreenUpdating = False
With ActiveSheet.Shapes("Button 1").DrawingObject
If .Caption = "Hide" Then
.Caption = "Show"
HideMe = True
Else
.Caption = "Hide"
HideMe = False
End If
For Each cll In Intersect(ActiveSheet.UsedRange, Columns(3)).Cells
If cll.Interior.Color = 123 Then cll.Resize(2).EntireRow.Hidden = HideMe
Next cll
End With
'Application.ScreenUpdating = True
End Sub


or:
Sub blah()
'Application.ScreenUpdating = False
With ActiveSheet
With .Shapes("Button 1").DrawingObject
If .Caption = "Hide" Then .Caption = "Show": HideMe = True Else .Caption = "Hide": HideMe = False
End With
For Each cll In Intersect(.UsedRange, .Columns(3)).Cells: If cll.Interior.Color = 123 Then cll.Resize(2).EntireRow.Hidden = HideMe
Next cll
End With
'Application.ScreenUpdating = True
End Sub

Sandler
05-26-2016, 06:51 PM
Thank you both :)

p45cal
05-27-2016, 02:24 AM
Thank you both :)It's nice that you said 'Thank you'.
What's not so nice is that you already had a solution to this at a cross posting at MrExcel hours ago. blanchard306 and I have wasted our time, utterly (flagged by the attachment I supplied still showing zero views). You could have said you already had a solution. I'm no moderator here (I am elsewhere) so I can't ask you to do anything.

Forums aren't operated by a bunch of automatons, but by humans, who have feelings.

The solution is easy; you include in your thread, links to all your cross posts, at all the forums involved. It's actually a rule at 99% of the forums. That way people can quickly check if you already have a solution and can then choose to improve on it or not, knowing how far down the line you are to getting the solution you want.

The reasons are crystallised here: http://www.excelguru.ca/content.php?184

Be considerate of others and they will be considerate to you. Don't be, and they won't.

Aussiebear
05-27-2016, 02:59 AM
I'm no moderator here (I am elsewhere) so I can't ask you to do anything.

Yes you can p45cal, your efforts here hold you in high standings, so don't feel intimidated about asking anyone to do anything.


Forums aren't operated by a bunch of automatons, but by humans, who have feelings.

Who also feely give their time and effort to assist others in a quest to find answers to problems. Please don't devalue their effort to you ( in a direct sense) and the community (indirectly by way of education).

Sandler
05-27-2016, 06:09 AM
I agree p45cal, I apologize and will be more mindful going forward.

I also like your spreadsheet solution, it is very concise, and it makes sense to stick a small button into the actual sheet that I want the task completed in.