Solved

How to loop through Excel ListObject that has been filtered

Posted on 2014-03-24
9
5,174 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
  • 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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