EntireRow.Hidden and EntireColumn.Hidden are misbehaving

[AndrewCrosby]

Hi,

I've been encountering a problem with hiding EntireRow and EntireColumn. I've constructed a basic demo, the spreadsheet looks like this with 2 named ranges:


My worksheet

My code

In my code I have two Subroutines, which loop through the named ranges and hide columns:

Option Explicit




Private Sub HideColumns()
    Dim myCell As Range
    For Each myCell In Range("mycols").Cells
        Debug.Print myCell.Value
        myCell.EntireColumn.Hidden = (myCell.Value > 5)
    Next
End Sub


Private Sub HideRows()
    Dim myCell As Range
    For Each myCell In Range("myRows").Cells
        Debug.Print myCell.Value
        myCell.EntireRow.Hidden = (myCell.Value > 5)
    Next
End Sub

My problem


However they do not work reliably. Initially they work and will hide any columns/rows where the cell value is greater than 5, but then they stop working.


Currently:


when I run HideColumns and view the Immediate window it records this:

[COLOR=#333333][FONT=Segoe UI]
1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
[/FONT][/COLOR]

So it loops through all the columns in the myCol range. And it does hide any column where the value is greater than 5. This is what I want.


However, when I run HideRows and view the Immediate window it records this:

[COLOR=#333333][FONT=Segoe UI]
1
[/FONT][/COLOR]

So it seems to fall out of the loop after the first iteration. It does not record any error message.


Can any one advise what I am doing wrong? I have tried using full qualified range references (ie Sheets("Sheet1").Range("myRows") and Sheets("Sheet1").Range("myCols")) but they stop working too.


Microsoft Office Professional Plus 2010
Version 14.0.7106.5003


Thanks


Andrew

 

[RoryA]

How are your names defined?

 

[AndrewCrosby]

Hi Rory,

thanks for your response. They are global ranges:

myCols: =Sheet1!$B$1:$K$1
myRows: =Sheet1!$A$2:$A$11

Andrew

 

[RoryA]

I can't replicate that behaviour. Does HideRows ever work?

 

[AndrewCrosby]

Hi Rory,

Yes, bizarrely HideRows (and HideColumns) work if I a) change the code b) move the worksheet to another workbook. But only for a while - then one or both of them stop working. I am wondering if it is specific to my PC/Office install. I've looked in the Event Viewer to see if there are any obvious events associated with Excel but I can't see any.

I've tried rebooting my machine and copying across the cells I am using (rather than the whole worksheet) to another Workbook, but it still stops working after a while. I don't have any other programs running that could be using PC resources and it is such a simple routine I am not sure why it should be causing problems.

I am going to continue the development on a different PC and see if I can replicate the error.

I may have to talk to my IT department about an Excel rebuild.... Unless I am missing something obvious!

Andrew