Why it this code fail to hide rows ?

[Designer6]

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

[RonMcK]

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

Have you tried adding some debug code? Insert the following code after the line where you assign a value to RowRangeValue, open the view immediate window, then, step through your code and see what is happening.

[vba]Debug.Print RowRangeValue, HiddenRow
[/vba]
Is your logic counting cells on sheet2 that link to blank cells on sheet1? Can you add code in column H that sums the number of columns in your range that are non-empty? Then you can test that value for each line.

Regards,

Ron
Orlando, FL

[mdmackillop]

[VBA]
Dim RowRangeValue As String, cel As Range

RowRangeValue = ""
For Each cel In RowRange
RowRangeValue = RowRangeValue & cel.Text
Next
If Len(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
[/VBA]

[Bob Phillips]

Its the formulae

[vba]

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) - Application.CountIf(RowRange, 0)

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
[/vba]

[mdmackillop]

RowRangeValue = Application.CountA(RowRange) - Application.CountIf(RowRange, 0)

Much better than mine!

[Designer6]

[vba]Debug.Print RowRangeValue, HiddenRow
[/vba]

I really like this one ^^^^^^

[Designer6]

Its the formulae

[vba]
RowRangeValue = Application.CountA(RowRange) - Application.CountIf(RowRange, 0)

[/vba]

Are there anyway i can tell it to ignore the data in a hidden column? For example, if i hide column C in sheeet1 and sheet2 it will mess up the rest of the data output on sheet2.

[Bob Phillips]

I think you would be best to use MDs method and check each column for hidden as you iterate through the columns.

[Designer6]

I think you would be best to use MDs method and check each column for hidden as you iterate through the columns.

What is MDs method ?

[RonMcK]

What is MDs method ?

MD is mdmackillop; see his first entry in this thread, the 3rd msg if I'm counting correctly. He shows you how to test each cell in the range on a row and set the .Hidden property of the row to True. Xld suggests that you test the .Hidden property of each column for the cells in the row so your logic skips counting hidden cells that have data in them.


Ron
Orlando, FL