• 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.

Dirty Maros...

Zach

Member
The attached file is a schedule that I'd like to create a cleaner macro to have it shrink down data to show only the yellow data rows and the dark blue header rows basically starting at row 10. everything above row 10 can stay. I used the recorder to manually create the macro but there has to be a cleaner way to do it. I have a master column that shows the color requirements per row and I use that in the other macros. Can a macro be created to read that column and hide cells to create a Master Summary tab?
 

Attachments

  • testing file.xlsm
    74.7 KB · Views: 4
Welcome back Zach. :)
How's this little gem?
Code:
Sub FilterColor()
Dim lastRow As Long
Dim i As Long
 
'How far down do we look?
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With
 
Application.ScreenUpdating = False
For i = lastRow To 10 Step -1
    With Cells(i, "D")
        'Hide everything that's gray colored
        .EntireRow.Hidden = (.Interior.Color = RGB(128, 128, 128))
    End With
Next i
Application.ScreenUpdating = True
 
End Sub
 
I forgot a fun little piece...the sheet is supposed to be protected.
I've attached the updated sheet. How much should that change the marco?
 

Attachments

  • testing file.xlsm
    76.2 KB · Views: 2
Hi, Zach!

If you accept to have the password hardcoded into the VBA project, it's just a matter of adding a first line:
Activeworksheet.Unprotect "xxx"
and a last line:
Activeworksheet.Unprotect "xxx"

If not I'm afraid you won't be able to do it, since even if you can build code for filtering and unfiltering by color and assign this to 2 buttons (like Complete and Undo), and protect the worksheet to allow autofilter, this option (of color) is not available for protected worksheets.

Regards!
 
To elaborate on what SirJB7 stated, the whole code would look like this:
Code:
Sub FilterColor()
Dim lastRow As Long
Dim i As Long
'Define your password in 1 single spot
Const myPassword = "abcd1234"
 
 
'How far down do we look?
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With
 
Application.ScreenUpdating = False
ActiveSheet.Unprotect myPassword
For i = lastRow To 10 Step -1
    With Cells(i, "D")
        'Hide everything that's gray colored
       .EntireRow.Hidden = (.Interior.Color = RGB(128, 128, 128))
    End With
Next i
ActiveSheet.Protect myPassword
Application.ScreenUpdating = True
 
End Sub
 
Back
Top