Consulting

Results 1 to 7 of 7

Thread: Object Required Error in UnHiding rows

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    Object Required Error in UnHiding rows

    I am trying to unhide all hidden rows in a range in ActiveSheet.Range("A4:A93")
    For each of those hidden ranges, when it now becomes visible, I want the Cell in Column A once it's now visible / unHidden to be shaded Interior.colorIndex = 19
    I'm getting an error in the [PROBLEM] lines, An Object Required Error

    I've tried using Row & Cell etc..etc... but I'm still getting an error

    For Each Row In ActiveSheet.Range("A4:A93")
        If Row.EntireRow.Hidden = True Then
                              [PROBLEM] EntireRow.Hidden = False
                                  [PROBLEM] EntireRow.Interior.colorIndex = 19

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not tested, and done from memory

    "Row" is a VBA key word, but I think the real problem was that "EntireRow" was not qualified, i.e. it should have been something like rCell.EntireRow

    Try

    For Each rCell In ActiveSheet.Range("A4:A93").Cells
        With rCell.EntireRow
           If .Hidden Then
              .Hidden = False
              .Interior.colorIndex = 19
           End If
        End With
    Next
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Thanks Paul_Hossler

    It worked. You are correct. "EntireRow" was not qualified, and also apparently,
    You cannot call the EntireRow.Hidden from the Cell object as I was attempting in my other trials.
    Sleep does wonders for the brain !
    Thank you.

  4. #4
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    What is the best way to modify this so that only the cells in Column A are shaded .Interior.colorIndex = 19

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    .cells (1).interior.colorindex = 19
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Probably something like this


    For Each rCell In ActiveSheet.Range("A4:A93").Cells
        With rCell
           If .EntireRow.Hidden Then          .EntireRow.Hidden = False          .Interior.colorIndex = 19
           End If
        End With Next
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Paul_Hossler & p45cal

    Thanks guys.
    Everything worked as expected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •