Same basic code, two files, one presents error.

[PhilW_34]

Hello,

I have two different worksheets with different sized headers. That is the only difference between the files. I want my macro to hide all rows where the cells in a range are blank. It works great on both files if I have at least two entries in the range. If I have zero (all blanks) or 1, the code fails in File 2. On execution of File 2 Code, I am getting Error 400. ErrorCatch says "Unable to set the Hidden Property of the Range Class."

The code works fine on file 1.

File 1 Code

Sub Show_OrderQtyOnly()
    'Show only cells with order quantity.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Unprotect Sheet
    Sheets("Sheet_Name").Unprotect Password:="Password"
        
    'Clear filters
    ActiveSheet.AutoFilterMode = False
    Range("A6:H6").AutoFilter
    
    'Hide Rows
    'I first had a the problem with this file but adding the line below (= False)
    'fixed it on this file.
    Range("C7:C2130").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    Range("C7:C2130").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    
    'Protect Sheet
    If ActiveSheet.ProtectContents = False Then
        Sheets("Sheet_Name").Protect Password:="Password", DrawingObjects:=True, _
        Contents:=True, AllowFiltering:=True
    End If
    
    Application.ScreenUpdating = True
End Sub

This code, in File 2 crashes if I have all blank cells in the range or only 1 entry.

Sub Show_OrderQtyOnly()
    'Show only cells with order quantity.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo Errorcatch
    
    'Unprotect Sheet
    Sheets("Sheet_Name").Unprotect Password:="Password"
        
    'Clear filters
    ActiveSheet.AutoFilterMode = False
    Range("A16:H16").AutoFilter
    
    'Hide Rows
'This Range is really the only difference between the two seemingly identical macros.
    Range("C17:C2140").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    Range("C17:C2140").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    
    'Alternative Hide Rows - Takes longer. Hides rows with zeros though. Would rather not use.
    'Dim ColC As Range
    'Dim Cell As Range

    'Set ColC = Range(Cells(17, 3), Cells(2140, 3))
    'For Each Cell In ColC
    'If Cell.Value = 0 Then
    'Cell.EntireRow.Hidden = True
    'End If
    'Next Cell
    
    'Protect Sheet
    If ActiveSheet.ProtectContents = False Then
        Sheets("Sheet_Name").Protect Password:="Password", DrawingObjects:=True, _
        Contents:=True, AllowFiltering:=True
    End If
    Exit Sub
Errorcatch:
MsgBox Err.Description
    Application.ScreenUpdating = True
End Sub

Can anyone help? Basically I have an order form. The specified range is where people can enter order quantities of part numbers listed in column D. For printing, I want to hide all rows with no quantities ordered. Ok, it works as it, but it bothers me that it crashes if the code is executed with zero or 1 entry in the range. I guess I'm just curious why it works on one file and not the other. Thanks in advance for your input.

Phil

 

[Ralajer]

Phil,

It looks like the SpecialCells method does some internal bounds checking and will only apply itself to a used range. If the bounds are at the top it won't return a range then setting the property hidden to a nothing range throws the error. Here is a work around that seems odd but corrects the error that I was able to reproduce on my machine.

    Range("C2141").Value = "A"
    Range("C17:C2140").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    Range("C17:C2140").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    Range("C2141").Value = Empty

Let me know if it works for you.

 

[Ralajer]

Thinking on it more it might be better to use an autofilter as you also wanted to hide of zeros. The below will hide zeros and blanks

Sub UseAutoFilter()
    ActiveSheet.AutoFilterMode = False
    '// Assume row 16 as headers 
    Range("A16:H2140").AutoFilter Field:=3, Criteria1:=">0"
End Sub

 

[PhilW_34]

Hello Rob,

I appreciate you taking a look at this. I feel a bit foolish because I did not think of using autofilter. It was right there the whole time. I've spent a lot of time trying to fix this and you've given me the answer. Thank you very much.

Phil