Filtering Listview

[kalle82]

Hi!

Im working in excel 2003.

I use a function to populate a listview control.

It's reads every cell and put it nicely into the listview.

BUT when i try to filter what items to populate the listview with I end up with an empty list view.

I do already filter it in the code im going to show... But i think I get stuck on my IF STATMENT.

WHAT I WANT TO DO BUT NOT SEEM TO ACCOMPLISH IS:

Check if subitems(6) is empty
THIS WORKS
If lvwItem.SubItems(6) = ""
AND CHECK IF
subitem2 is empty if it is I want my program to exclude that row not populate the listview with i.

Really grateful for tips on how to do this!


Here's the FOR LOOP iterating through the cells.

For lngRow = 4 To lngEndRow
            lngCol = 1
            lngItemIndex = 0
                                    
            Set lvwItem = .ListItems.Add(, , ws.Cells(lngRow, lngCol).Value)
            Dim n As Integer
           n = n + 1
           
            
            For lngCol = 2 To lngEndCol
            lngItemIndex = lngItemIndex + 1
           
                Dim testet As Integer
                
                testet = Len(lvwItem.SubItems(3))
                
                
                                
                If lvwItem.SubItems(6) = "" And testet >= 3 Then
                
                Else
                
                               
                    ListView1.ListItems.Remove (n)
                
                    lngCol = 17
                    n = n - 1
                
                        GoTo sture

                End If

Full code for whole SUB is here.

Sub readlist4()

    Dim ws As Worksheet
    Dim lngRow As Long
    Dim lvwItem As ListItem
    Dim lngEndCol As Long
    Dim lngCol As Long
    Dim lngEndRow As Long
    Dim lngItemIndex As Long
    Dim lvwItem2 As ListItem
    
    ' Sätter det som ska läsas in
        
    Set ws = Worksheets("Ärenden")
       
    lngEndCol = ws.Range("A3:Q3").End(xlToRight).Column
    lngEndRow = ws.Range("A3:Q3").End(xlDown).Row
    
    lngRow = 3
    With ListView1
        .View = lvwReport
        For lngCol = 1 To lngEndCol
                                
        If lngCol <= 2 Or lngCol = 12 Then
            .ColumnHeaders.Add , , ws.Cells(lngRow, lngCol).Value
        
        ElseIf lngCol = 16 Then
        
        .ColumnHeaders.Add , , ws.Cells(lngRow, lngCol).Value
        
        ElseIf lngCol = 17 Then
        
        .ColumnHeaders.Add , , ws.Cells(lngRow, lngCol).Value, 180
        
        ElseIf lngCol >= 3 Or lngCol <= 14 Then
        
             .ColumnHeaders.Add , , ws.Cells(lngRow, lngCol).Value, 0
        End If
        
        Next
        
        For lngRow = 4 To lngEndRow
            lngCol = 1
            lngItemIndex = 0
                                    
            Set lvwItem = .ListItems.Add(, , ws.Cells(lngRow, lngCol).Value)
            Dim n As Integer
           n = n + 1
           
            
            For lngCol = 2 To lngEndCol
            lngItemIndex = lngItemIndex + 1
           
                Dim testet As Integer
                
                testet = Len(lvwItem.SubItems(3))
                
                
                                
                If lvwItem.SubItems(6) = "" And testet >= 3 Then
                
                Else
                
                               
                    ListView1.ListItems.Remove (n)
                
                    lngCol = 17
                    n = n - 1
                
                        GoTo sture

                End If

                lvwItem.SubItems(lngItemIndex) = ws.Cells(lngRow, lngCol).Value
                       
               
sture:
                
                'End If
           
            
            Next
       
        Next
        
    End With

End Sub

 

[SkipVought]

hi,

I'd suggest that you STEP thru your code and observe what the valus of your variables are (are they as expected) and he state of the Objects that you are manipulating.

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

I have done that...

If really confused over one thing..

If I use this statement:

If lvwItem.SubItems(6) = "" And testet <= 3 Then

Only the cells i want to filter appear... Good but how about doing it the other way around...

If lvwItem.SubItems(6) = "" And testet >= 3 Then

Nothing get into the listview...

Which leads me to that there must be something going on with the index, but I'm to lsot to figure it out..

ListView1.ListItems.Remove (n)
                
                    lngCol = 17
                    n = n - 1

 

[SkipVought]

try

                If lvwItem.SubItems(6) = "" And testet >= 3 Then
                
                [s]Else[/s]
                
                    ListView1.ListItems.Remove (n)
                
                    lngCol = 17
                    n = n - 1
                
                    GoTo sture

                End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Worked like a charm! Thanks Skip Im indebted! Tell me if you need anything