Count Half of the Visible Rows

[adamsm]

Hi,

The following code counts the visible rows. For example if 5 rows are visible out of 10 rows, it says 5 of 10 records.
Any help to change the code so that it counts half of the visible rows would be kindly appreciated.For example if 2 rows are visible the display message would be as 1 of 10 records.

If 4 rows are visible the message would display as 2 rows.

 Sub CountVisRows()
Dim rng As RangeSet rng = ActiveSheet.AutoFilter.Range 
MsgBox rng.Columns(1). _   
SpecialCells(xlCellTypeVisible).Count - 1 _   & " of " & rng _   .Rows.Count - 1 & " Records" 
End Sub

[Bob Phillips]

Why?

[vba]

Sub CountVisRows()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
MsgBox (rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1) \ 2 & _
" of " & rng.Rows.Count - 1 & " Records"
End Sub
[/vba]

[p45cal]

[VBA]MsgBox (rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1) / 2 & " of " & rng.Rows.Count - 1 & " Records"[/VBA]?

[adamsm]

Thanks for the help p45cal & xld. That was what I needed. I do really appreciate it.

By the way in reply to xld I must say according to my workbook the suggested codes work better.

[Bob Phillips]

Works better? The answer is just wrong.

[adamsm]

Why is the answer wrong?

[Bob Phillips]

As you said youreslef ... If 4 rows are visible the message would display as 2 rows.

I may not know much but I know that is not accurate.

[adamsm]

That's because I want to count only half of the visible rows.

[Bob Phillips]

I know that, I just cannot see why. Butwe are going round in circles, and it is not important, so let's drop it.