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

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
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

VBA Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
VBA Code:
    Set rRange = wSht.Range("rngData")


with this one?
VBA Code:
    Set rRange = wSht.ListObjects("Table1").Range
 
Upvote 0
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
VBA Code:
Set rRange = rRange.Rows.SpecialCells(xlCellTypeVisible)
is doing I can see what the following is doing:

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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!

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top