PDA

View Full Version : Count Half of the Visible Rows



adamsm
06-29-2010, 10:32 AM
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
06-29-2010, 10:44 AM
Why?



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

p45cal
06-29-2010, 10:47 AM
MsgBox (rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1) / 2 & " of " & rng.Rows.Count - 1 & " Records"?

adamsm
06-29-2010, 10:56 AM
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
06-29-2010, 11:17 AM
Works better? The answer is just wrong.

adamsm
06-29-2010, 11:19 AM
Why is the answer wrong?

Bob Phillips
06-29-2010, 12:15 PM
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
06-30-2010, 01:43 AM
That's because I want to count only half of the visible rows.

Bob Phillips
06-30-2010, 02:21 AM
I know that, I just cannot see why. Butwe are going round in circles, and it is not important, so let's drop it.