Counting and suming number of visible rows

[abberyfarm]

Hello,

The code below counts the number of rows, my question is, how would you count the number of visible rows?

Totalrows = ch1.Range("D" & Rows.count).End(xlUp).Row

also

How would you calculate the sum of the visible rows in column D, from D2 downwards?

I'm trying to write a formula,

Prob = "Subtotal sum of column D" / The number of visible cells"

Thanks for the help

john

 

[BiocideJ]

In answer to your direct question, this will give you the number of visible cells in column D (including cells that are blank)

visCells = Range("D:D").SpecialCells(xlCellTypeVisible).Count

You can use the .End(xlUp) to limit your range to just the usedcells in the column.

In answer to your underlying question which seems to be how to get the average of the visible cells in column D, you could always use the oft forgotten SUBTOTAL formula with the 'exclude hidden' version of the Average function (101)

Prob = WorksheetFunction.Subtotal(101, Range("D:D"))

 

[abberyfarm]

Hi BiodideJ,

Thanks for the reply.

I tried this code, but it always returns Totalrows = 1, which is incorrect.

Totalrows = ch1.Range("D:D").SpecialCells(xlCellTypeVisible).End(xlUp).count

Is this the correct syntax for the subtotal function? It doesn't seem to summing the number correctly.

 Prob = WorksheetFunction.Subtotal(101, ch1.Range("D:D"))

For example, here totalrows should equal 9 and the subtotal should be 0.

Excel Workbook
	A	B	C	D
1	SOC	Parking Time	Distance	Decision
11	95	35	30	0
28	95	40	32	0
89	90	30	30	0
111	95	20	36	0
130	95	45	32	0
146	95	30	32	0
208	95	5	34	0
209	90	20	30	0
230	95	15	30	0
Charging

 

[abberyfarm]

bump

 

[Marcelo Branco]

Assuming data in Sheet1 beginning in row 2 (headers in row 1), maybe...

Sub aTest()
    Dim ch1 As Worksheet, Totalrows As Long, Prob As Long, dataRange As Range
    
    Set ch1 = Sheets("Sheet1")
    
    With ch1
        Set dataRange = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
        Totalrows = dataRange.SpecialCells(xlCellTypeVisible).Count
        MsgBox Totalrows
        Prob = WorksheetFunction.Subtotal(101, dataRange)
        MsgBox Prob
    End With
    
End Sub

M.

 

[BiocideJ]

Here is a sample test code given your examples.

They both appear to do exactly what you are wanting.

Sub test()
Set ch1 = Sheets("Sheet1")
Prob = WorksheetFunction.Subtotal(101, ch1.Range("D:D"))
visCells = ch1.Range("D2:D" & ch1.Range("D" & ch1.Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Count
End Sub

The reason your visCells count was one was because you were getting the list of visible rows and then doing .END(xlUP) which would take you to a single row and then getting a count (of that single row). That was why the answer was always 1. I didn't really explain that part well so that was my fault.


EDIT: Marcelo's example is good as well, although if you use his, you will need to change the data type of Prob from Long to Double as it is an average.

 

[Marcelo Branco]

Correction

Use
Dim Prob as Double

M.

 

[abberyfarm]

Thanks for the help guys.