Only View Highlighted Rows

[Guest]

Excel 2003 - How can I just view rows highlighted in yellow? I have an excel
spreadsheet with about 2000+ rows but I only want to view the rows that are
actually highlighted in yellow.

Thanks!

 

[JW]

One way. This could potentially take a few seconds to run though. It
is looking at column A to determine the interior colorindex of the
row.
Sub hider()
Dim lRow As Long
Cells.EntireRow.Hidden = False
lRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 2 To lRow
If Not Cells(i, 1).Interior.ColorIndex = 36 Then _
Cells(i, 1).EntireRow.Hidden = True
Next i
End Sub

 

[JW]

I'm not a fan on making selections in code at all, but this version
does run much quicker than the other I posted. Just giving you
options.
Sub test()
Dim selMade As Boolean, lRow As Long
selMade = False
Cells.EntireRow.Hidden = False
lRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 2 To lRow
If Not Cells(i, 1).Interior.ColorIndex = 36 Then
If selMade = False Then
Cells(i, 1).Select
selMade = True
Else
Union(Selection, Cells(i, 1)).Select
End If
End If
Next i
Selection.EntireRow.Hidden = True
End Sub

 

[David McRitchie]

Depends on how the color occurs, and if yellow is really yellow.
For instance if the color is as a result of Conditional Formatting
you won't detect it with that macro.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

One way. This could potentially take a few seconds to run though. It
is looking at column A to determine the interior colorindex of the
row.
Sub hider()
Dim lRow As Long
Cells.EntireRow.Hidden = False
lRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 2 To lRow
If Not Cells(i, 1).Interior.ColorIndex = 36 Then _
Cells(i, 1).EntireRow.Hidden = True
Next i
End Sub

Excel 2003 - How can I just view rows highlighted in yellow? I have an excel
spreadsheet with about 2000+ rows but I only want to view the rows that are
actually highlighted in yellow.

Thanks!

 

[JW]

David, that is absolutely true. I was going on the assumption that
the OP was referring to cells that had their background color
physically changed to a specific color. Thanks for throwing that word
of caution out there!

Regards,
Jeff

Depends on how the color occurs, and if yellow is really yellow.
For instance if the color is as a result of Conditional Formatting
you won't detect it with that macro.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

One way. This could potentially take a few seconds to run though. It
is looking at column A to determine the interior colorindex of the
row.
Sub hider()
Dim lRow As Long
Cells.EntireRow.Hidden = False
lRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 2 To lRow
If Not Cells(i, 1).Interior.ColorIndex = 36 Then _
Cells(i, 1).EntireRow.Hidden = True
Next i
End Sub

Excel 2003 - How can I just view rows highlighted in yellow? I have an excel
spreadsheet with about 2000+ rows but I only want to view the rows that are
actually highlighted in yellow.

Thanks!