Count non empty cells on sheet with hidden cols

[fraser5002]

Hi,

Im trying to count the number of non empty cells on a worksheet but there are hidden columns on the sheet. The code im using counts them too how can i count only the "non hidden , non empty cells "

using the following code at the moment:



Nocells = WorksheetFunction.CountA(Sheets("Results").UsedRange)

[georgiboy]

There is probably a better way but you could use a loop to go through columns and check if they are hidden then count.

Sub CountNonHidden()
Dim c As Range
Dim x As Integer

For Each c In Sheet1.Columns

If c.EntireColumn.Hidden = False Then
x = x + WorksheetFunction.CountA(c)
End If

Next

MsgBox x

End Sub

Hope this helps

[Bob Phillips]

I think that you would have to ikterate all the columns, find the hidden ones,
count the cells in those, and subtract them.

[MaximS]

try to use that:


Sub Count_Visible()

Dim LCol, i, j As Long

'This will check for last used column
LCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

j = 0

For i = 1 To LCol
If Columns(i).Hidden = False Then
j = j + WorksheetFunction.CountA(Columns(i))
End If
Next i

End Sub