![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Guest
Posts: n/a
|
I currently have a macro that runs through the spreadsheet and highlights all rows yellow that have a particular value in column C. Let's say the value is "5". Sometimes this value is the only one in the cell, and sometimes it is part of a list (i.e. "1,2,3,4,5,6"). These list rows are also highlighted. What I can't figure out how to do is to hide all of the rows that are NOT highlighted by the above macro. I haveplaced the code below. Thanks in advance...
Sub search() With Worksheets(1).Range("c1:c6345") Set c = .Find("04680-00", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.Pattern = xlPatternWhite50 c.EntireRow.Interior.ColorIndex = 2 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End Sub |
|
|
|
#2 |
|
Board Regular
Join Date: Feb 2002
Posts: 255
|
I've never been a big fan of the find method because it fails often and when it fails the programs crashes. Instead I would do this:
Sub search() Worksheets(1).Activate Cells(1, 3).Select Do Until ActiveCell.Row = 500 '(any criteria that makes the search stop works here) If InStr(1, ActiveCell.Value, "04680-00") <> 0 Then With ActiveCell .Interior.Pattern = xlPatternLightUp (whatever pattern you used didn't work on my version of excel) .EntireRow.Interior.ColorIndex = 2 End With Else ActiveCell.EntireRow.Hidden = True End If ActiveCell.Offset(1, 0).Select Loop End Sub Good luck! Dave |
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|