Unhiding/Hiding Rows within VBA

[KaibleBasha]

Hi,

I found this piece of code from a reply by HalfAce (or MrExcel MVP).


Private Sub Worksheet_Calculate()
Dim LstRw As Long, Rw As Long
Application.ScreenUpdating = False

'''Define LstRw as the last row in column F with data.

LstRw = Cells(Rows.Count, "F").End(xlUp).Row

'''Un-hide all rows to start with

Range("F4:F" & LstRw).EntireRow.Hidden = False

''' Go through column F, (starting at row 4) _ & hide all rows with a value of 1

For Rw = 4 To LstRw
If Cells(Rw, "F") = 1 Then Cells(Rw, "F").EntireRow.Hidden = True
Next Rw

Application.ScreenUpdating = True

End Sub


I copied it & modified it slightly, only to to fit my report. So it looks like this;

Private Sub Worksheet_Calculate()
Dim LstRw As Long, Rw As Long
Application.ScreenUpdating = False
'''Define LstRw as the last row in column B with data.


LstRw = Cells(Rows.Count, "B").End(xlUp).Row


'''Un-hide all rows to start with


Range("B7:B" & LstRw).EntireRow.Hidden = False


''' Go through column B, (starting at row 7) & hide all rows with a value of 1


For Rw = 7 To LstRw
If Cells(Rw, "B") <> Cells(2, "I") Then Cells(Rw, "B").EntireRow.Hidden = True
Next Rw
Application.ScreenUpdating = True


End Sub



My question is how do I get his to unhide all rows after the value of "WorkersName" ("I2") is nothing e.g. I delete the workers name. As currently this will only unhide the rows up to the last row that is not hidden. E.g. I run this and rows 34,35,39 are not hidden, then I delete the worker name from I2. At this point, only 39 of the 100 rows are revealed.

Thanks & sorry I am a n00b.

Kai

 

[Fluff]

Hiya
Try changing

Range("B7:B" & LstRw).EntireRow.Hidden = False

to

Cells.EntireRow.Hidden = False

HTH

 

[KaibleBasha]

Hi! Thanks for replying

I have just tried what you suggest, however, I am now getting this error message;

Run-time error ‘-2147417848 (80010108)’:
Method ‘Hidden’ of object ‘Range’Failed.


Just to be clear I changed the following;

'''Un-hide all rows to start with

Range("B7:B" & LstRw).EntireRow.Hidden = False


To;

Cells.EntireRow.Hidden = False

Thanks again

Kai

 

[Fluff]

Hello Kai
Not quite sure what's going wrong as I can't reproduce the error. I've also tried it on another m/c running 2010 on XP & that works as well.
As a matter of interest what are you running, for excel/os?

 

[KaibleBasha]

Hey,

Thanks again fro trying.

I am running Windows 7 Professional & Excel 2007.

 

[Fluff]

Hi
Unless it's something to do with 2007 I can see no reason why this shouldn't work & cannot replicate the error you are getting.

Private Sub Worksheet_Calculate()
    Dim LstRw As Long
    Dim Rw As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

'Define LstRw as the last row in column B with data.
    LstRw = Cells(Rows.Count, "B").End(xlUp).Row

'Un-hide all rows to start with
    Cells.EntireRow.Hidden = False

'Go through column B, (starting at row 7) & hide all rows with a value of 1
    For Rw = 7 To LstRw
        If Cells(Rw, "B") <> Cells(2, "I") Then Cells(Rw, "B").EntireRow.Hidden = True
    Next Rw

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

One thing I would recommend when running a worksheet_Calculate event is turning EnableEvents to false, otherwise you could end-up in a permanent loop.
Sorry I could not be of more help