Hiding rows based on color

[Steve68]

I am a totally lost newbie in VBA and am quickly learning from reading the board that I don't know much about Excel in general. I didn't know you could do so much in Excel!
What I want to do is use VBA or a macro to go thru column "j" of a spreadsheet and hide the rows that are cell color index 37. It is a read only spreadsheet and I just need to filter out the items I do not need to see. It sounds simple, but I have been unable to figure it out.

Anyone willing to help me out here?

Thanks,
Steve Thompson

 

[shippey121]

welcome to the board

try

Sub hideoncol()
For Each c In Range("J1:J50")
If c.Interior.ColorIndex = 37 Then
c.EntireRow.Hidden = True
End If
Next
End Sub

to enter code press ALT + F11
right click on your workbook name in the left panel and select insert module, now paste the code above in the large right window,

to run code press ALT + F8 and double click HIDEONCOL

to adjust range alter the J50 to suit

hth

 

[erik.van.geit]

Hi, Steve68
Welcome to the Board !!!!!

there are very fast methods (without loop) to do this, but as you are new, here is a "basis" macro

Option Explicit

Sub test()
Dim LR As Long      'Last Row
Dim RN As Integer   'Row Number

LR = Cells(Rows.Count, "J").End(xlUp).Row

Application.ScreenUpdating = False

    For RN = 1 To LR
    If Cells(RN, "J").Interior.ColorIndex = 37 Then Cells(RN, "J").EntireRow.Hidden = True
    Next RN
    
 Application.ScreenUpdating = True
 
End Sub

to speed up at least a bit there is screenupdating set to "false"
take a look in the helpfiles for every "keyword" you encounter
(just click in the word and hit function key F1)

kind regards,
Erik

 

[Steve68]

Thanks for the help. I will try them out tomorrow at work. It is strange, I can look at the code and understand what it is doing, but I can't seem to find sample code and change it to suit what I want to do. Writers block?

Steve Thompson

 

[shippey121]

the easiest way to find out how to do something in VBA is to use the macro recorder,click macro >> record, now do what you lets say color a cells background, so click a colour and click the cell you want and now stop the recorder you will see code like

Sub Macro1()
' Macro1 Macro
' Macro recorded 16/11/2006 by C Shippey

    Range("A1").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

now try altering it a little because the recorder includes all statements which you might not require the easiest way is the put a ' in front of the line, the line will go green and be ignored when the code is ran, in the above example the code could be changed to

Sub macro99()
Range("a1").Interior.ColorIndex = 6
End Sub

i would personally never use the WITH ....END WITH if i had a range of cells i would put

Range("A1:C1")

but then im not the best VBA coder anyway

but what you learn from the recorder is the statements that you need in this case

interior.colorindex =

now you can just put your IF statements round it and you are writing code, although this can be used for most tasks its no good for everything, the recoder would create a LOOP everytime you did something it would just add more code but when reading you could delete most of it and include a LOOP

HTH

 

[apb9981]

Hi, Steve68
Welcome to the Board !!!!!

there are very fast methods (without loop) to do this, but as you are new, here is a "basis" macro

Option Explicit

Sub test()
Dim LR As Long      'Last Row
Dim RN As Integer   'Row Number

LR = Cells(Rows.Count, "J").End(xlUp).Row

Application.ScreenUpdating = False

    For RN = 1 To LR
    If Cells(RN, "J").Interior.ColorIndex = 37 Then Cells(RN, "J").EntireRow.Hidden = True
    Next RN
    
 Application.ScreenUpdating = True
 
End Sub

to speed up at least a bit there is screenupdating set to "false"
take a look in the helpfiles for every "keyword" you encounter
(just click in the word and hit function key F1)

kind regards,
Erik

This worked perfectly for me, thank you!

 

[excelbyryan]

This is great! I'm hoping to leverage this but apply it across all columns. EX. If any cell in that row is colored, do not hide. Else, hide all rows that do not contain a colored cell in any column. Any advice?