Hi All,

I've been working on this code for a while but for somehow it doesn't do what i really wanted. It fails to hide rows even only one column has the data



Private Sub Worksheet_Activate()

      Dim HiddenRow&, RowRange As Range, RowRangeValue&

      '*****************************
      '< Set the 1st & last rows to be hidden >
      Const FirstRow As Long = 4
      Const LastRow As Long = 20

      '< Set your columns that contain data >
      Const FirstCol As String = "B"
      Const LastCol As String = "G"
      '*****************************
      
      ActiveWindow.DisplayZeros = False
      Application.ScreenUpdating = False

      For HiddenRow = FirstRow To LastRow

            '(we're using columns B to G here)
            Set RowRange = Range(FirstCol & HiddenRow & _
                                 ":" & LastCol & HiddenRow)

            'sums the entries in cells in the RowRange
            On Error Resume Next
            RowRangeValue = Application.CountA(RowRange.SpecialCells(xlCellTypeVisible).Value)

            If RowRangeValue <> 0 Then
                  'there's something in this row - don't hide
                  Rows(HiddenRow).EntireRow.Hidden = False
            Else
                  'there's nothing in this row yet - hide it
                  Rows(HiddenRow).EntireRow.Hidden = True
            End If

      Next HiddenRow

      Application.ScreenUpdating = True

End Sub