An Addition to the Workbook Open Event

[Shay A]

Hi,

what should I add to my code in order for a message box to pop up alarming that in the active sheet there are either filtering/hidden rows or columns?

Private Sub Workbook_Open()

Worksheets(1).Activate
Range("A3").Select


End Sub


TY!

 

[Chihiro]

Why alarm only? Why not remove filtering and/or hidden rows/columns upon workbook oen?

 

[Shay A]

I would prefer to have it in a different macro. Do you happen to have something for me?

Thanks again!
Shay

 

[Chihiro]

To unhide all rows and columns...

Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

I don't recommend hidden row/column detection on workbook open. As far as I know you'd need to loop through range column by column and row by row to detect.

Something like below to check for hidden row and/or columns.

Dim i As Long, x As Long
x = 0
With Worksheets(1).Cells(3, 1).CurrentRegion
    For i = 1 To .Rows.Count
        If .Rows(i).Hidden = True Then
            x = i
        End If
    Next
    For i = 1 To .Columns.Count
        If .Columns(i).Hidden = True Then
            x = i
        End If
    Next
    If x >= 1 Then
        MsgBox "There is hidden row and/or column"
    End If
End With

For filter it depends on what you want to check for. Do you want presence of Filter checked or only when Filter is applied and rows hidden?

 

[shrivallabha]

I have not tested much but should work mostly.

Sub Test()
Dim lCnt1 As Long, lCnt2 As Long
lCnt1 = ActiveSheet.UsedRange.Cells.Count
lCnt2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Count
If lCnt1 <> lCnt2 Then
    MsgBox "Some cells in this sheet are hidden!", vbInformation
End If
End Sub

 

[Shay A]

It works tx!
Now, would you recommend to embed this cide upon worksheet activation? Not a workbook.

 

[Shay A]

To unhide all rows and columns...

Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

I don't recommend hidden row/column detection on workbook open. As far as I know you'd need to loop through range column by column and row by row to detect.

Something like below to check for hidden row and/or columns.

Dim i As Long, x As Long
x = 0
With Worksheets(1).Cells(3, 1).CurrentRegion
    For i = 1 To .Rows.Count
        If .Rows(i).Hidden = True Then
            x = i
        End If
    Next
    For i = 1 To .Columns.Count
        If .Columns(i).Hidden = True Then
            x = i
        End If
    Next
    If x >= 1 Then
        MsgBox "There is hidden row and/or column"
    End If
End With

For filter it depends on what you want to check for. Do you want presence of Filter checked or only when Filter is applied and rows hidden?

Hello again,
I would like to add to the code the cancel of frozen panes. What wuld be the addition?

TY

 

[Chihiro]

To remove frozen panes, you'd use ActiveWindow.

ActiveWindow.FreezePanes = False

FYI - If you are looping through more than one worksheet, you'd need to activate worksheet for each iteration.