How do I count a column in excel leaving out hidden rows

GRiTech,

Something like this:

With Sheets("Sheet1")
Set rng = Range(.cells(1,1),.cells(.cells.rows.count).end(xlup))
end with

For each celle in rng
If celle.hidden = false then
    counter = counter +1
End if
next celle

Msgbox counter

Patrick

Thanks for your reply Joao

=SUBTOTAL(102;A:A)  

Gives me an error on the function_num

What is function_num 102 supposed to do

Im using Excel 2002 if thats any help

The formula i suggested is for Count.

For CountA use:

=SUBTOTAL(103,A:A)

Regards,

Joao

Or perhaps:

=SUBTOTAL(3,A:A)

Patrick

Excel 2002 does not have the 100 range for subtotal.

The difference between

Subtotal(3,A:A) and Subtotal(103,A:A) is that 103 ignores hidden cells and 3 includes all cells.

This was introduced in Excel 2003.

=SUBTOTAL(3,A:A)

Ignores cells that are "hidden" as a result of filtering only (it doesn't ignore rows hidden with "Hide"). If you change to 103 (in Excel 2003 or later) then that ignores both filtered out and hidden (with Hide)

regards, barry

I can get a total using =SUBTOTAL(3,A:A) ... but as you say Joaol it includes all cells

Subtotal(103,A:A)  returns #Value

have tried your macro Patrick but get

Runtime error 1004

Unable to get hidden property of range class

Debug highlights "If celle.Hidden = False Then"

Maybe this will help

The macro im using to hide the cells is this

Sub hide()
Application.ScreenUpdating = False

Dim rng As Range, cell As Range, rng1 As Range, lrow As Long
lrow = Cells(65536, "A").End(xlUp).Row

Set rng = Range("a2:a" & lrow)
Set rng1 = Range("d2:d" & lrow)

For Each cell In rng
If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then
x = Application.Evaluate("Max(if(" & rng.Address & "=""" & cell.Value & """," & rng1.Address & ",""""))")
If x <> cell.Offset(0, 3).Value Then
cell.EntireRow.Interior.ColorIndex = 3
cell.EntireRow.Hidden = True
End If
End If
Next cell
Application.ScreenUpdating = True
 

End Sub

If you just want to count the number of distinct values in column A you can use a formula like this

=SUMPRODUCT((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1000&""))

that works whether you have anything hidden or not......

regards, barry

Thats looking good Barry

Just testing it now ... seems to be working though

Cheers

G

Barry

My data goes down to 2496 so I have adjusted to

=SUMPRODUCT((A3:A2496<>"")/COUNTIF(A3:A2496,A3:A2496&""))

which seems to work

It falls over if it hits a cell with no data though ... is there anyway to stop that as the data might be to 1000 or might be 3496

The formula is designed to cope with blank rows so you should just be able to extend the range to a size greater than'll you'll ever need, e.g. perhaps

=SUMPRODUCT((A1:A5000<>"")/COUNTIF(A1:A5000,A1:A5000&""))

regards, barry

hmmm yes i had tried that earlier

if i use =SUMPRODUCT((A3:A3000<>"")/COUNTIF(A3:A3000,A3:A3000&""))

I get

#div/0! error

Ill try your alternative

Barry

Not sure about the alternative

=SUM(IF(FREQUENCY(IF(A1:A5000<>"",MATCH(A1:A5000,A1:A5000,0)),ROW(A1:A5000)-ROW(A1)+1),1))

on a sheet with nothing hidden i get 2353

using a simple

=COUNTA(A:A)-2

I get 2493

OK, but depending upon the data those are possible good results if you have some values duplicated.

What sort of data do you have in column A, numbers or text (or both)? Can you post the workbook (or a sample?)

regards, barry

OK sorry ive been off sick, Ill try and post part of the spreadsheet today or tomorrow.