January 26, 2005

Color Hidden Rows 2

Just a quick improvement on the previous code.

Sub ColorHiddenRows()
    On Error GoTo Terminator
    Dim rw As Range
    Dim fstrw As Range
    Dim myRange As Range
    Dim myColor As Long
    Dim myPattern As Long
    Dim myPatternColor As Long
    Set myRange = Selection
    For Each rw In myRange.Rows
        If rw.Hidden = True Then
            If fstrw Is Nothing Then
                Set fstrw = rw
            Exit For
        End If
    End If
    Next
    fstrw.Select
    Application.Dialogs(xlDialogPatterns).Show
    myColor = ActiveCell.Interior.ColorIndex
    myPattern = ActiveCell.Interior.Pattern
    myPatternColor = ActiveCell.Interior.PatternColorIndex
    For Each rw In myRange.Rows
        If rw.Hidden = True Then
            rw.Interior.ColorIndex = myColor
            rw.Interior.Pattern = myPattern
            rw.Interior.PatternColorIndex = myPatternColor
        End If
    Next
    Exit Sub
Terminator: MsgBox "There are no hidden rows ", vbExclamation
End Sub

This code allows you to select a color and/or pattern from a built-in Excel dialog box. A choice of either allows far more freedom if you want to revert to your original formatting later.

Have flu, am going to bed. Night.  

Posted by andrewe at 23:47

January 23, 2005

Color Hidden Rows

Here's a couple of macros that might be useful for working with hidden rows.

Why color hidden rows? If you are working with an unfamiliar file, it's very easy to unhide rows but then you have to remember which rows were hidden. This should help when the time comes.

Select a suitable column or add one if you like.

Sub ColorHiddenRows()
    Dim rw As Range
    For Each rw In Selection.Rows
        If rw.Hidden = True Then
            rw.Interior.ColorIndex = 34
        End If
    Next
End Sub

Your hidden rows should now be easy to spot unless you are using Light Turquoise somewhere else in your sheet. (No problem, change the color or use something like rw.Value = "Hidden")

When it's time to hide them again, select the same column and run this.

Sub HideColoredRows()
    Dim rw As Range
    For Each rw In Selection.Rows
        If rw.Interior.ColorIndex = 34 Then
            rw.Hidden = True
        End If
    Next
End Sub

When you are finished, don't forget to leave the sheet as you found it. (Making a copy before you start might be a good idea)

Comments? Check here for details.  
Posted by andrewe at 19:08

January 01, 2005

Happy New Year!

Happy New Year!

I've been on vacation starting last Thursday and I'm putting the time to good use studying more about Excel.

My latest "project" should be finished in a few days. In the meanwhile, I'd like to thank everyone for their support in Year 2004, I look forward to blogging again soon in Year 2005.  
Posted by andrewe at 00:00