CODEWITHSUNDEEP
vbaexcel
neilm
neilm

Reputation: 3

vba Row numbers for filtered rows

I have this code to capture the row number in a filtered table, do some things and then go to the next visible row. I works great for the first row.

Sub test()

Dim rn As Long
Dim cell As Range
Dim rng As Range


    Set rng = Sheets("FabricatedParts").Range("A:A").SpecialCells(xlCellTypeVisible)
        rn = Sheets("FabricatedParts").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Row
         For Each cell In rng
            With ActiveSheet
                .Range("B14").Value = [Parts].Cells(rn, [Parts[Part.]].Column)
            End With
            'rn = Next Visible Row #???.row
         Next cell

End Sub

When I change to offset(1, 0) to offset(2, 0) it still returns the row number for the first visible row(in this case 10). I am looking for a VBA statement that will return the row number for the next visible row and pass that number to the rn variable. For the filtered range I am testing the next row number is 11, the next 165, the next 166 etc. Thank you for your time.

Upvotes: 0

Views: 6787

Answers (1)

OldUgly
OldUgly

Reputation: 2119

Use the row number of the cell loop.

Sub test()

Dim rn As Long
Dim cell As Range
Dim rng As Range


    Set rng = Sheets("FabricatedParts").Range("A:A").SpecialCells(xlCellTypeVisible)
         For Each cell In rng
            rn = cell.row
            With ActiveSheet
                .Range("B14").Value = [Parts].Cells(rn, [Parts[Part.]].Column)
            End With
         Next cell

End Sub

Upvotes: 2

Related Questions