PDA

View Full Version : Why it this code fail to hide rows ?



Designer6
02-20-2008, 10:26 AM
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
02-20-2008, 10:59 AM
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.

Debug.Print RowRangeValue, HiddenRow

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
02-20-2008, 11:24 AM
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

Bob Phillips
02-20-2008, 11:27 AM
Its the formulae



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

mdmackillop
02-20-2008, 11:30 AM
RowRangeValue = Application.CountA(RowRange) - Application.CountIf(RowRange, 0)

Much better than mine!

Designer6
02-20-2008, 01:08 PM
Debug.Print RowRangeValue, HiddenRow


I really like this one ^^^^^^

Designer6
02-20-2008, 01:13 PM
Its the formulae


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



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
02-20-2008, 03:16 PM
I think you would be best to use MDs method and check each column for hidden as you iterate through the columns.

Designer6
02-21-2008, 02:18 PM
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
02-21-2008, 02:32 PM
What is MDs method ?

MD is mdmackillop (http://vbaexpress.com/forum/member.php?u=87); 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