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

PhilW_34

Board Regular
Joined
Jan 4, 2007
Messages
128
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
Thanks Fluff. I ticked out my code and tried running this. I'm getting an error on "If rngRowsToHide Is Nothing Then"

Rich (BB 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
 
Upvote 0
Try declaring the variables
Code:
Dim rngRowsToHide As Range, rngColsToHide As Range, Cl As Range
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top