counting non hidden rows in VBA

[fraz627]

After filtering a worksheet I would like to sequentially number each row for the non hidden rows

row status
1 visible 1
2 hidden
3 hidden
4 visible 2
5 visible 3

Thanks

 

[MARK858]

Seeing as you haven't said anything about where your data is. The below is based on data being in column A and the result in column B (amend to suit).

Sub NumVis()
    Dim myCell As Range, x As Long
    x = 1
    Application.ScreenUpdating = False
    For Each myCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12)
        myCell.Offset(, 1).Value = x
        x = x + 1
    Next
    Application.ScreenUpdating = True
End Sub

 

[farmerscott]

As an alternative........the result is in Col B.

Sub visible_rows()

Dim lr As Long


lr = UsedRange.Rows.Count
Count = 1
For y = 1 To lr Step 1

    If Rows(y).Hidden = False Then
    
        Cells(y, 2).Value = "Visible" & Count
        Count = Count + 1
     End If
     
Next y

End Sub

 

[fraz627]

thanks that worked.