How to loop through Excel ListObject that has been filtered

I have a listobject table defined and then filter the list using this code:

ActiveSheet.ListObjects(syncTable).Range.AutoFilter field:=EntIDColix, Criteria1:=enterprise_id
ActiveSheet.ListObjects(syncTable).Range.AutoFilter field:=VMRUColix, Criteria1:=isVM
ActiveSheet.ListObjects(syncTable).Range.AutoFilter field:=EntLOBIDColix, Criteria1:=enterprise_lob_id

Open in new window

This works to filter the table, now I want to loop over the visible rows.  How can I do that and also use the table column names to get values from the cells?
mamusciaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mamusciaConnect With a Mentor Author Commented:
I actually got an answer from Jan Karel Pieterse website here - he's a MS MVP.  This works.

Dim oCell As Range 
    For Each oCell In ActiveSheet.ListObjects(1).ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible) 
        MsgBox Intersect(oCell.EntireRow, ActiveSheet.ListObjects(1).ListColumns("a").DataBodyRange).Value 
    Next

Open in new window

0
 
englanddgCommented:
I haven't done this in years, but I'd say, for VBA...

Have the values of the control regenerate onClick (or some other method)
0
 
nutschConnect With a Mentor Commented:
Use something like Listobject("something").listcolumns("something").databodyrange.specialcells(xlcelltypevisible) to get a range containing visible cells only.

Sorry if I'm not exact, not next to excel right now.

Thomas
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mamusciaAuthor Commented:
Can either of you expand your answers.  Right now I have it working, but in effect I'm still scanning all rows, not just the visible rows.

This works, but is inefficient.  If you can provide a detailed example of how I would loop through only the visible rows, that's what I'm looking for.

NUTSCH - I tried the code you provided, but it does not compile.  I also tried a number of things using SPECIALCELLS(xlcelltypevisible), but that property is not supported in the manner used.

Here's the code that currently loops through all rows and then only uses the visible rows.

Set oTable = ActiveSheet.ListObjects(syncTable) '.DataBodyRange.SpecialCells(xlCellTypeVisible)
    For Each lstRow In oTable.ListRows
        If (lstRow.Range.EntireRow.Hidden) Then
            ' skip it
        Else
            ' process it
           myqty = Intersect(lstRow.Range, oTable.ListColumns("Quantity").Range).value
            End If
        End If
    Next lstRow

Open in new window

0
 
nutschCommented:
Gimme an hour or so to get to a computer and I'll test.
0
 
mamusciaAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for mamuscia's comment #a39953593

for the following reason:

Through additional research on another website I found out how to accomplish this.
0
 
nutschCommented:
Considering the elements of the code I had available at the time, I've provided the right solution in comment 39952426, which is confirmed by the code that asker found online.

No issue with accepting that last comment as the final solution, but I think I should at least get a strong assist.

Thomas
0
 
mamusciaAuthor Commented:
Yes, I would agree.  You gave me a good lead.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.