How to Make CountA Function Not to Count Hidden columns data

[Designer6]

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

[Bob Phillips]

But isn't that right. If they have data, they have data, hidden or not.

Otherwise, as you are talking columns, that means they are not hidden by filtering so you will need to cycle through them to count.

[mdmackillop]

If you want to count only the non-hidden cells, make this change

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

[Designer6]

But isn't that right. If they have data, they have data, hidden or not.

Otherwise, as you are talking columns, that means they are not hidden by filtering so you will need to cycle through them to count.

Since i am a newbie. I don't know how to answer but it sounds logical to me

[Designer6]

If you want to count only the non-hidden cells, make this change

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


It sort of works, when two columns are unhided it failed at row #15 and when 3 columns are enabled it failed at row #18 with the following message

Run-time error '1004'
No cells were found

I attached the excel file to hope it helps.
1. click on the role selection button
2. Among checkboxes, you can only select checkbox 1,2 and 5 in any combination you want

[mdmackillop]

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

[Bob Phillips]

Since i am a newbie. I don't know how to answer but it sounds logical to me

Which bit?

[Bob Phillips]

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

If you are going to go that way, zeroise RowRangeVale before the onerror.

[mdmackillop]

zeroise???
:funnyashe

[Designer6]

If you are going to go that way, zeroise RowRangeVale before the onerror.

What do you mean zeroise RowRangeValue.... It that means set it equals to zero ?

[Designer6]

Since, there are only four characters (A,O, P and S) going to be in each row. I think i could write a case statement to check for each rows for those characters. Or assign a value to each of those letter like a number 1 and use a sum statement

[Designer6]

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

It works great on two or more columns are unhided. But it fails to to hide empty rows if one column is unhided

[Bob Phillips]

What do you mean zeroise RowRangeValue.... It that means set it equals to zero ?



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

[Designer6]

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

It didn't do anything. Could you tell me why it fails if one column is enable? It really bothers me. One thing i notice is that RowRangeValue always equal to 1 if one column is enabled. So that fail the code