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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.