Quick hiding of rows in WS_Activate ()

[Imbecill]

Hi all,

Bernie was kind to suggest a sub to hide rows quick. Can any of you just
help me to modify Bernies Sub to a sheet specific code, placed in Sub
Worksheet_Activate () ??

I have today a most inefficient code with a For...To... Step approach (look
in bottom of this message). The column to check in can variate between A to
C depending on sheets. Cells in B:B are not quite empty, they contain
formulas leaving a "" in the cell.

*******

Imbecill,
In the codemodule of the Thisworkbook object, paste this code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
On Error Resume Next
Sh.Cells.EntireRow.Hidden = False
Sh.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

This assumes empty means empty, not blank from a formula returning "".

HTH,
Bernie
MS Excel MVP

**************
I use now For...To...Step to hide rows, but it take to long, I would like a
instant hiding if possible.

Further, depending on what sheet it is, the number used rows are flexible


Sub HideEmptyRows_B_4_125()
'
' Hiding rows- Look in Column 2 (B) and treat row 4 to125
ActiveSheet.Unprotect
Dim Rw As Integer
For Rw= 4 To 125 Step 1
If Cells(Rw, 2) <> "" And Cells(Rw,
2).EntireRow.Hidden = True Then
Cells(Rw, 2).EntireRow.Hidden = False
ElseIf Cells(Rw, 2) = "" And Cells(Rw,
2).EntireRow.Hidden = False Then
Cells(Rw, 2).EntireRow.Hidden = True
End If
Next
' ActiveSheet.Protect AllowFormattingCells:=False
' ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

 

[Frank Kabel]

Hi
so you want to use Bernie's code but only for a specific sheet?. If yes
try:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
if lcase(sh.name)<>"your_specific_sheet" then exit sub
Application.ScreenUpdating = False
On Error Resume Next
Sh.Cells.EntireRow.Hidden = False
Sh.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

You've got one more reply at the other thread.

(slightly different than Frank's response)