Capturing Table AutoFilter Values on a ListBox (rows hidden getting in the way?)

[RawlinsCross]

I have a table that has 7 columns and 122 rows.
Columns: Date, Time, East/West, Status, Activity, Comments, Date+Time

The Date and Time are separated and I use that last column for time filtering. It's that third column that's giving me all the trouble - East/West. When I filter for either "East" or "West" the table shows all the relevant entries so no problem there. But when I try to convert it to a listbox via the code below, hidden rows are getting in the way of transferring the table result to the listbox.

For example, if I filter for 'east' and rows 2, 3, 4, 5 are east, east, east, west (i.e. row 5 is hidden) - then I'll get rows 2, 3, 4 transferred to the listbox - no problem
, if I filter for 'east' and rows 2, 3, 4, 5 are east, west, east, west (i.e. rows 3, 5 are hidden) - then I'll only get row 2 transferred to the listbox even those the table shows all east entries

Just FYI - I have three optionbuttons choosing East/West/or Both

Private Sub SummarizeData()

Dim wSht As Worksheet
Dim rRange As Range
Dim tempArray() As Variant
Dim i As Long

Set wSht = ThisWorkbook.Worksheets("RawData")
Set rRange = wSht.Range("rngData")

If wSht.AutoFilterMode Then wSht.AutoFilter.ShowAllData

'Filter the table for time (dStartDate_PVar and dEndDate_PVar are public variables set by two DTPickers on another userform)
'dStartTime_PVar and dEndTime_PVar are public variables for time
wSht.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:=">=" & CDbl(dStartDate_PVar + dStartTime_PVar), Operator:=xlAnd, Criteria2:="<=" & CDbl(dEndDate_PVar + dEndTime_PVar)

'For East West do another filter
'Each of these work - when I go to the worksheet it's all been filtered correctly
If Me.obShowEast.Value = True Then
wSht.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="=East"
ElseIf Me.obShowWest.Value = True Then
wSht.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="=West"
ElseIf Me.obShowBoth.Value = True Then
wSht.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="=East", Operator:=xlOr, Criteria2:="=West"
End If

'This is where it goes wonky.  If I have an east, east, east, west it'll show 3 easts
'If I have a east, east, west it'll show 2 easts
'If I have a east, west, east it'll show only the first east
Set rRange = rRange.Rows.SpecialCells(xlCellTypeVisible)

tempArray = rRange.Value

'Second column is time which has to be formated
For i = 1 To UBound(tempArray)
tempArray(i, 2) = Format(tempArray(i, 2), "h:mm AM/PM")
Next i
'Populate the ListBox
Me.lbHistory.List = tempArray
Me.lbHistory.Selected(0) = True
Me.lblCount.Caption = Me.lbHistory.ListCount

End Sub

 

[rlv01]

I wonder if you are not having a problem with your rRange definition not covering the full range of the table. What happens if you replace this line of code

    Set rRange = wSht.Range("rngData")

with this one?

    Set rRange = wSht.ListObjects("Table1").Range

 

[RawlinsCross]

Just tried your suggestion - still the same phenomenon. The table in the worksheet looks great - all selected direction (i.e east/west) are showing up fine but the listbox transfer is not happening. I'm trying it another way so I can more closely step through the code. Where as I can't see what

Set rRange = rRange.Rows.SpecialCells(xlCellTypeVisible)

is doing I can see what the following is doing:

For Each r In rRange.Rows
     If Not r.Hidden Then
         If rFilterRange Is Nothing Then
             Set rFilterRange = r
        Else
             Set rFilterRange = Union(rFilterRange, r)
        End If
    End If
Next r

tempArray = rFilterRange.Value

So from this second code, I can follow 'r' in a watch window and rFilterRange in a Local window. I can see the row number of r and it's visible property moving from False to True and skipping over the True and I can also see rFilterRange correctly building thoughout the process (even after hidden rows).

So the offending line is the tempArray = rFilterRange.Value

So it worked before when I wasn't filtering east/west and I was only filtering time. The only difference between the two is that when I was filtering time, the resulting filtered range was continuous contrasted with when I filter direction (east/west), now I have a non-continuous range.

Is there an issue transferring a non-continuous range to an array via the route above?

John

 

[Fluff]

You cannot load non-contiguous ranges into an array like that.
You can either copy the filtered range to a temp sheet & load the array from there, or pull the whole table into the array & then loop through copying the data you want into another array

 

[RawlinsCross]

Hi Fluff - yeah, I figure the first of your two options is preferable. So went to work and this works fine. Lesson learned on the 'non-contiguous' front. Hiding away from Miss Corona anyhow!

Private Sub SummarizeData()

Dim wSht As Worksheet
Dim rRange As Range
Dim tempArray() As Variant
Dim i As Long


Set wSht = ThisWorkbook.Worksheets("RawData")
Set rRange = wSht.Range("rngData")

If wSht.AutoFilterMode Then wSht.AutoFilter.ShowAllData

'Filter the table (dStartDate_PVar and dEndDate_PVar are public variables set by two DTPickers on another userform)
'dStartTime_PVar and dEndTime_PVar are public variables for time
wSht.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:=">=" & CDbl(dStartDate_PVar + dStartTime_PVar), Operator:=xlAnd, Criteria2:="<=" & CDbl(dEndDate_PVar + dEndTime_PVar)

'For East West do another filter
'Each of these work - when I go to the worksheet it's all been filtered correctly
If Me.obShowEast.Value = True Then
     wSht.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="=East"
ElseIf Me.obShowWest.Value = True Then
     wSht.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="=West"
ElseIf Me.obShowBoth.Value = True Then
     wSht.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="=East", Operator:=xlOr, Criteria2:="=West"
End If

Set rRange = rRange.Rows.SpecialCells(xlCellTypeVisible)

'Paste to new worksheet
Application.ScreenUpdating = False
Set wSht = ThisWorkbook.Worksheets("DataWorkUp")
wSht.Range("A1").CurrentRegion.ClearContents
rRange.Copy
wSht.Range("A1").PasteSpecial
Application.ScreenUpdating = True

'Set the now CONTIGUOUS range
Set rRange = wSht.Range("A1").CurrentRegion

tempArray = rRange.Value

'Second column is time which has to be formated
For i = 1 To UBound(tempArray)
     tempArray(i, 2) = Format(tempArray(i, 2), "h:mm AM/PM")
Next i
'Populate the ListBox
Me.lbHistory.List = tempArray
Me.lbHistory.Selected(0) = True
Me.lblCount.Caption = Me.lbHistory.ListCount

End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback