Solved

How to loop through Excel ListObject that has been filtered

Posted on 2014-03-24
9
5,524 Views
Last Modified: 2014-03-28
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
Comment
Question by:mamuscia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 3

Expert Comment

by:englanddg
ID: 39952320
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
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 500 total points
ID: 39952426
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
 

Author Comment

by:mamuscia
ID: 39953430
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:nutsch
ID: 39953457
Gimme an hour or so to get to a computer and I'll test.
0
 

Accepted Solution

by:
mamuscia earned 0 total points
ID: 39953593
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
 

Author Comment

by:mamuscia
ID: 39953670
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
 
LVL 39

Expert Comment

by:nutsch
ID: 39953671
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
 

Author Comment

by:mamuscia
ID: 39954117
Yes, I would agree.  You gave me a good lead.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question