Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""
Results 1 to 8 of 8

Thread: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular PhilW_34's Avatar
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    Hello,

    I'm updating a file I've used for a long time. In this file, I'd manually enter data in cells A5:A16. If I only entered in one cell in that range, the rest were blank. However, I've decided to upgrade the file and now I'm using formulas to populate data in A5=A16. My .specialCells(xlCellTypeBlanks) is no longer working on the cells that only look blank because the formula result is "". Other code below hides corresponding rows below my original range. I still need to do that.

    Ultimately, how can I revise xlCellTypeBlanks to something like = ""? THanks.

    Phil

    Code:
    Sub Hide_Rows()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
     With ActiveSheet 
            On Error Resume Next
            Set rngRowsToHide = .Range("A5:A16").SpecialCells(xlCellTypeBlanks)  '<- Need to fix this to make it work right again.
            Set rngColsToHide = .Range("F4:X4").SpecialCells(xlCellTypeBlanks)      '<- Need to fix this to make it work right again.
    
            
            On Error GoTo 0
        End With
        
        If Not rngRowsToHide Is Nothing Then
            Set rngRowsToHide = Union(rngRowsToHide, _
                                    rngRowsToHide.Offset(23), _
                                    rngRowsToHide.Offset(39), _
                                    rngRowsToHide.Offset(55), _
                                    rngRowsToHide.Offset(71))
     
            rngRowsToHide.EntireRow.Hidden = True
        End If
        On Error Resume Next
        If Not rngColsToHide Is Nothing Then
            Set rngColsToHide = Union(rngColsToHide, _
                                    rngColsToHide.Offset(0, 24))
            rngColsToHide.EntireColumn.Hidden = True
        End If
        Columns("Z:AF").EntireColumn.Hidden = False
        On Error Resume Next
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    End Sub
    Is it wrong that I have more fun working in Excel than others in my office? I didn't think so.

    Windows 10 & Office 365

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,594
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    You will need to loop through the ranges like
    Code:
       For Each Cl In .Range("A5:A16")
          If Cl.Value = "" Then
             If rngRowsToHide Is Nothing Then Set rngRowsToHide = Cl Else Set rngRowsToHide = Union(rngRowsToHide, Cl)
          End If
       Next Cl
       For Each Cl In .Range("F4:X4")
          If Cl.Value = "" Then
             If rngColsToHide Is Nothing Then Set rngColsToHide = Cl Else Set rngColsToHide = Union(rngColsToHide, Cl)
          End If
       Next Cl
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular PhilW_34's Avatar
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    Thanks Fluff. I ticked out my code and tried running this. I'm getting an error on "If rngRowsToHide Is Nothing Then"

    Code:
    Sub Hide_Rows()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
     With ActiveSheet 
     '   On Error Resume Next
            For Each Cl In .Range("A5:A16")
                If Cl.Value = "" Then
    'Runtime error 424 here ->  If rngRowsToHide Is Nothing Then Set rngRowsToHide = Cl Else Set rngRowsToHide = Union(rngRowsToHide, Cl)
                    End If
            Next Cl
            For Each Cl In .Range("F4:X4")
                If Cl.Value = "" Then
                If rngColsToHide Is Nothing Then Set rngColsToHide = Cl Else Set rngColsToHide = Union(rngColsToHide, Cl)
                    End If
            Next Cl
            
     '       Set rngRowsToHide = .Range("A5:A16").SpecialCells(xlCellTypeBlanks)
    '        'Set rngColsToHide = .Range("F4:X4").SpecialCells(xlCellTypeBlanks)
    
            
    '        On Error GoTo 0
    End With
        
    
    '    If Not rngRowsToHide Is Nothing Then
    '        Set rngRowsToHide = Union(rngRowsToHide, _
                                    rngRowsToHide.Offset(23), _
                                    rngRowsToHide.Offset(39), _
                                    rngRowsToHide.Offset(55), _
                                    rngRowsToHide.Offset(71))
     
    '        rngRowsToHide.EntireRow.Hidden = True
    '    End If
    '    On Error Resume Next
    '    If Not rngColsToHide Is Nothing Then
    '        Set rngColsToHide = Union(rngColsToHide, _
                                    rngColsToHide.Offset(0, 24))
    '        rngColsToHide.EntireColumn.Hidden = True
    '    End If
    '    Columns("Z:AF").EntireColumn.Hidden = False
    '    On Error Resume Next
        ActiveSheet.Range("$A$90:$C$306").AutoFilter Field:=2, Criteria1:="<>"
        Range("B17").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    End Sub
    Is it wrong that I have more fun working in Excel than others in my office? I didn't think so.

    Windows 10 & Office 365

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,594
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    Try declaring the variables
    Code:
    Dim rngRowsToHide As Range, rngColsToHide As Range, Cl As Range
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular PhilW_34's Avatar
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    Thanks again Fluff.

    That took care of the runtime error. However, when I run the code, the screen just blinks and no rows or columns hide. Hmmm...

    Thanks,

    Phil
    Is it wrong that I have more fun working in Excel than others in my office? I didn't think so.

    Windows 10 & Office 365

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,594
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    That code was just to replace this
    Code:
            Set rngRowsToHide = .Range("A5:A16").SpecialCells(xlCellTypeBlanks)  '<- Need to fix this to make it work right again.
            Set rngColsToHide = .Range("F4:X4").SpecialCells(xlCellTypeBlanks)      '<- Need to fix this to make it work right again.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular PhilW_34's Avatar
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    Hi Fluff,

    And...Now you're my hero. Kicking tail and taking names on a Saturday. Many thanks! I was too smart for my own good. Once I removed the ticks from the rest of the code, it worked perfectly. Thanks again.

    Phil
    Is it wrong that I have more fun working in Excel than others in my office? I didn't think so.

    Windows 10 & Office 365

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,594
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide Rows and Columns code revision - xlCellTypeBlanks changed to formuala result ""

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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