Designer6
02-13-2008, 08:48 AM
Hi Guys,
I'm using countA to tell whether a row is blank or not. However, i am facing with a problem. The problem is that countA counts the hidden columns with data also could anyone help me solve the problem.
Sub HideEmptyRows()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
Dim A As Integer, O As Integer, P As Integer
'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 11
Const LastRow As Long = 254
'< Set the columns that may contain data >
Const FirstCol As String = "P"
Const LastCol As String = "AV"
'*****************************
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
RowRangeValue = Application.CountA(RowRange.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
I'm using countA to tell whether a row is blank or not. However, i am facing with a problem. The problem is that countA counts the hidden columns with data also could anyone help me solve the problem.
Sub HideEmptyRows()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
Dim A As Integer, O As Integer, P As Integer
'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 11
Const LastRow As Long = 254
'< Set the columns that may contain data >
Const FirstCol As String = "P"
Const LastCol As String = "AV"
'*****************************
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
RowRangeValue = Application.CountA(RowRange.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