Is there a way to loop through the visible cells in a table (listobject) column?-CodePudding

In an Excel table (ListObject), I've begun tracking hundreds of movies and their status as I rip them to disc to use in Plex. The table is nothing fancy; it stores things you'd expect like the ripping folder, the Plex media folder, the name of the movie, the year it was released, the backup folder where I store a copy of the raw disc files, etc. I created the workbook after I had already done several hundred movies to make tracking easier. As a result of inconsistency in my methods before I created the workbook, I now have some cleanup to do in the workbook. I want to do it using VBA as manually corrections would be tedious, error-prone and take a long time. I have an algorithm that I believe will work, but it relies on the ability to loop through the visible cells in a column of a filtered table -- the column that I will use to determine if I need to make any corrections on that row, and then where needed, the columns where corrections need to be made.

Any guidance here would be appreciated! Thanks

CodePudding user response：

Sub Test()

Set MyTable = Worksheets("Sheet1").ListObjects("Table1")

With MyTable
    For i = 1 To .DataBodyRange.Rows.Count
        If .DataBodyRange.Rows(i).EntireRow.Hidden = False Then
            [Do Something]
        End If
    Next i
End With

End Sub

Replace [Do Something] with your desired action for the visible rows, for example:

Sub Test()

Set MyTable = Worksheets("Sheet1").ListObjects("Table1")

With MyTable
    For i = 1 To .DataBodyRange.Rows.Count
        If .DataBodyRange.Rows(i).EntireRow.Hidden = False Then
            .DataBodyRange(i, 3).Value = "I'm not hidden!"
        End If
    Next i
End With

End Sub

which enters "I'm not hidden!" into the third column of that table for each visible row.