• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hiding colored rows in excel of office xp version

A

ashish navale

New Member
Hi..

I have a report in which there are about 200-250 records. Out of these records there are some exceptional cases which are highlighted in yellow. I have to manually hide the rows which are yellow colored. As my company is using excel of office XP version I find it difficult to hide the colored rows..I know this facility is inbuilt in office 2007. But can't help as my company use office xp. I also searched on internet regarding this. But I saw there was some vba code which does it. I tried but it doesn't work. I am thinking to prepare a macro which will hide all yellow colored rows once macro is run.


Please help me with below:

1. How to hide colored rows in excel(Office XP) without using macro.

2. How to prepare macro to hide colored rows in Office XP excel.
 
Hi Ashish,


try this macro, please change the Columns and Rows refernce to suit your sheet


Sub Hidecolour()

For x = 15 To 1 Step -1 ' 15 is the last row in the range

Range("B" & x).Select ' assume B is the column of data

If Selection.Interior.Pattern <> xlNone Then

Selection.EntireRow.Hidden = True

End If


Next x


End Sub
 
Hello,


I cant understand what excatly is given could u plz help me with more details. Like when I start recording macro where excatly I have to enter the above details.
 
Hi Ashish,


For non-macro method:

1. Select range of cells which has coloring

2. Bring up the Find menu (Ctrl+F), click "options", and choose a format with color pattern that you are looking for

3. Do a "Find All"

4. In the drop down that appears, click on an entry to set the focus, then select all found cells by hitting Ctrl+A

5. Close the Find dialogue. Hit Ctrl+9 to hide all the currently select cells.

For macro solution, I've tweaked kchiba's code. You can copy it into a standard module (Alt F11, Right Click on Workbook Insert module) and then run it. All you need to change is the column call-out.

[pre]
Code: 
Sub Hidecolour()
Dim MyColumn As String
Dim LastRow As Integer

'Which column are you looking at?
MyColumn = "B"

LastRow = Cells(65536, MyColumn).End(xlUp).Row
For x = 1 To LastRow
With Cells(x, MyColumn)
If .Interior.Pattern <> xlNone Then
.EntireRow.Hidden = True
End If
End With
Next x

End Sub
[/pre]
 
Luke the code you gave works. Could you please also provide a code that can delete the colored rows? I am thinking to prepare a macro even for deletion of colored rows now. Please help me with it.
 
Ashish


This just requires two small changes to the Lukes code

[pre]
Code: 
Sub Delete_Color()
Dim MyColumn As String
Dim LastRow As Integer

'Which column are you looking at?
MyColumn = "B"

LastRow = Cells(65536, MyColumn).End(xlUp).Row
For x = LastRow To 1 Step -1 'Loop backwards
With Cells(x, MyColumn)
If .Interior.Pattern <> xlNone Then
.EntireRow.Delete 'Delete not Hide
End If
End With
Next x

End Sub
[/pre]

Noting that we loop backwards instead of forward, this is because once you delete a Row all the rows below them change Row numbers and so it is easier to keep track of loop backwards


Then we use Delete instead of Hidden
 
Hi,


I'm trying to modify this code to hide columns where the font colour in Row 3 is TintAndShade = -0.249977111117893


The last column is "OZ".


This is what i've got so far.

[pre]
Code: 
Sub Hidecolour()
Dim MyRow As Integer
Dim LastCol As String

'Which row are you looking at?
MyRow = "3"

LastCol = Cells(MyRow, OZ).End(xlUp).Column
For x = 1 To LastCol
With Cells(MyRow, x)
If .Interior.TintAndShade = -0.249977111117893 Then
.EntireColumn.Hidden = True
End If
End With
Next x

End Sub
[/pre]
Click to expand...

But I'm getting a 1004 error when i try and run it, any suggestions about what i've got wrong?


Thanks
 
Madmarki

Try changing this line


LastCol = Cells(MyRow, 416).End(xlUp).Column 'Oz=Column 416
 
Just had to change a few things.

[pre]
Code: 
Sub Hidecolour()
Dim MyRow As Integer
Dim LastCol As Integer
'Which row are you looking at?
MyRow = 3

LastCol = Cells(MyRow, "IV").End(xlToLeft).Column
For x = 1 To LastCol
With Cells(MyRow, x)
If .Interior.TintAndShade = -0.249977111117893 Then
.EntireColumn.Hidden = True
End If
End With
Next x

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top