• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6493
  • Last Modified:

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?
0
mamuscia
Asked:
mamuscia
  • 4
  • 3
2 Solutions
 
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
 
nutschCommented:
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nutschCommented:
Gimme an hour or so to get to a computer and I'll test.
0
 
mamusciaAuthor 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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now