Counting Selected/Highlighted Rows

P

Phil H

Need to modify this maco to not count hidden rows. For example if seven rows
of ten rows in question are hidden, I need the macro to count only the three
visible on screen.

Also, if no rows were hidden, and all ten were selected (highlighted), the
macro should count ten.

In other words, it should count only what is selected/visible on screen.

Sub CountHighlightedRows()
MsgBox "Rows Selected: " & Selection.Rows.Count
End Sub
 
J

JLGWhiz

Not sure if I understand, but try this:

Sub fj()
Dim x As Long
x = Selection.SpecialCells(xlCellTypeVisible).Count
MsgBox x
End Sub
 
J

JLGWhiz

I noticed an anomaly about using the xlVixible property to count the rows.
If you use the Rows.Count, it only counts to down to the first hidden row.
But if you only select one column and count the special cells visible
property, it give the correct count of rows not hidden.
 
J

JLGWhiz

This is sort of a gerry rig but it works and will do what you want.

Sub fl()
Dim x As Long
x = Selection.SpecialCells(xlCellTypeVisible) _
.Count / Selection.Columns.Count
MsgBox x
End Sub
 
R

Ryan H

This will count all the visible rows in your selection. Hope this helps! If
so, let me know, click "YES" below.

Option Explicit

Sub CountHighlightedRows()

Dim rw As Range
Dim VisibleRows As Long

For Each rw In Selection.Rows
If rw.Hidden = False Then
VisibleRows = VisibleRows + 1
End If
Next rw

MsgBox "Rows Selected: " & VisibleRows, vbInformation

End Sub
 
M

Mike H

Hi,

One way

Sub CountHighlightedRows()
For Each c In Selection.Rows
If c.RowHeight > 0 Then
viz = viz + 1
End If
Next
MsgBox viz
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
P

Phil H

Thanks for the help!

Ryan H said:
This will count all the visible rows in your selection. Hope this helps! If
so, let me know, click "YES" below.

Option Explicit

Sub CountHighlightedRows()

Dim rw As Range
Dim VisibleRows As Long

For Each rw In Selection.Rows
If rw.Hidden = False Then
VisibleRows = VisibleRows + 1
End If
Next rw

MsgBox "Rows Selected: " & VisibleRows, vbInformation

End Sub
 
P

Phil H

Thanks for the help!

Mike H said:
Hi,

One way

Sub CountHighlightedRows()
For Each c In Selection.Rows
If c.RowHeight > 0 Then
viz = viz + 1
End If
Next
MsgBox viz
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top