?
Solved

How to loop through Excel ListObject that has been filtered

Posted on 2014-03-24
9
Medium Priority
?
5,843 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 2000 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
Independent Software Vendors: 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

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.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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