Hiding rows macro not working

[AmezNez]

Hi everyone

I've written some code to hide certain rows based on the selection of a drop down box in a separate worksheet. The code relates to a cell named EntityE which is linked to the drop down box on the separate worksheet.

Here is the code I have:

Sub Worksheet_Calculate()
    Call FreezeScreen(True)
            If Range("EntityE").Value = "X1" Then
            Sheets("Expenses").Activate
            Range("WPFExpensesRows").Select
            Selection.EntireRow.Hidden = True
            End If
            If Range("EntityE").Value = "X2" Then
            Sheets("Expenses").Activate
            Range("WPFExpensesRows").Select
            Selection.EntireRow.Hidden = False
            End If
            If Range("EntityE").Value = "X3" Then
            Sheets("Expenses").Activate
            Range("WPFExpensesRows").Select
            Selection.EntireRow.Hidden = False
            End If
    Range("B7").Activate
    Sheets("Tax Account").Activate
    Call FreezeScreen(False)
End Sub

When the macro runs I get a runtime error 1004 saying "Unable to set the hidden property of the range class"

Does anyone know why this isn't working? Thanks!!

 

[Firefly2012]

Avoid Selecting - it's unnecessary and can result in runtime errors when included in sheet module code.

Try:

Sub Worksheet_Calculate()
    Call FreezeScreen(True)  ' i have no idea what this is doing so don't know if it will impact

Select Case Range("EntityE").Value
   Case "X1"
            Sheets("Expenses").Range("WPFExpensesRows").EntireRow.Hidden = True
   Case "X2"
            Sheets("Expenses").Range("WPFExpensesRows").EntireRow.Hidden = False
   Case "X3"
            Sheets("Expenses").Range("WPFExpensesRows").EntireRow.Hidden = False
End Select
'Range("B7").Activate
'Sheets("Tax Account").Activate
Call FreezeScreen(False)
End Sub

 

[AmezNez]

Thanks a lot FireFly

I've used your code now but I'm still getting the same error on

Sheets("Expenses").Range("WPFExpensesRows").EntireRow.Hidden = False

Any ideas whay this is happening?

I'm getting the same error on other macros I have in the workbook that are also hiding rows in other sheets.

Thanks

 

[Firefly2012]

It's rather hard for me to be definitive as I don't know what else is going on in your workbook - do any of these sheets have worksheet protection applied, for instance?

How has the range name "WPFExpensesRows" been defined? Is its scope workbook or worksheet?

 

[AmezNez]

There isn't any protection on the sheets or book. Also the range name has workbook scope. Thanks

 

[Firefly2012]

In the VBE, open the Immediate Window (if it isn't already) with Ctrl+g and type in the following and report back what the return values are:

?Range("WPFExpensesRows").Address

?Names("WPFExpensesRows").RefersTo

Range("WPFExpensesRows").EntireRow.Hidden = False

Don't omit the leading ? in the first two above

 

[AmezNez]

Here are the results:

?Range("WPFExpensesRows").Address
$11:$12,$27:$46,$51:$52
?Names("WPFExpensesRows").RefersTo
=Expenses!$11:$12,Expenses!$27:$46,Expenses!$51:$52
Range("WPFExpensesRows").EntireRow.Hidden = False

Then I get the Unable to set the Hidden property of the Range class error on the last one.

Thanks

 

[Firefly2012]

I'm not sure why this is failing at the moment - any chance you could email me the file? It can be a completely stripped down version (ie no data) AS LONG AS it still exhibits the problem. I will PM you my email address in case you are able.

 

[Jonmo1]

Can you hide/Unhide the same rows (ALL of the same rows) by hand?

Do you have any objects on the sheet at those rows, like Buttons/Pictures/Drawings/TextBoxes/etc.. ?

 

[AmezNez]

Thanks for your responses.

Yes I can hide the rows by hand and no I don't have any buttons etc in there.

It's very odd because it seems to go through the Select Case within the Worksheet calculate event then causes an error on the simple bit at the end which is just:

Range("A10").Activate
Sheets("Tax Account").Activate

so that when the macro has finished running it ends back on the right sheet without lots of rows highlighted.

(Firefly I'm afraid I cannot share the full file, I know this is really unhelpful of me but its confidential stuff!)

Thanks all for your help