VBA Filter missing the last row.

[Darren Bartrup]

Hi all,

I'm having an annoying problem where I have a small routine to filter a data range to whichever column and argument I pass to it.

I've put the routine at the end of this post.
It's called using this:

SetFilter rMainData, Array(7, "=" & DateValue(Date)), Array(13, "=")

This is meant to filter by column 7 equaling todays date and column 13 is blank, but it misses the last row (which meets the criteria and should be shown but is hidden instead).

My workbook doesn't have any subtotals, rMainData references the whole range in question.
I've tried changing DateValue(Date) to:
DateSerial(Year(Date),Month(Date),Day(Date)) and
Format(DateValue(Date),"dd/mm/yyyy") but that doesn't work.

I've tried extending the range by another row - still misses it.

The only way I can get it to work is by filtering it manually - which I can't do for this project.

The filtering procedure is here and it works most of the time, just not now

Public Sub SetFilter(rDataRange As Range, ParamArray sFilters())
    Dim wrkSht As Worksheet
    Dim x As Long
    
    Set wrkSht = rDataRange.Parent 'Get reference to worksheet.
    
    With wrkSht
        If Not .AutoFilterMode Then rDataRange.AutoFilter 'Turn on the auto-filter
        If .FilterMode Then .ShowAllData 'Clear any filters applied.
    End With
    With rDataRange
        For x = LBound(sFilters) To UBound(sFilters)
            Select Case UBound(sFilters(x))
                Case 1 '2 elements to array.
                    .AutoFilter Field:=sFilters(x)(0), Criteria1:=sFilters(x)(1)
                Case 3 '4 elements to array.
                    .AutoFilter Field:=sFilters(x)(0), Criteria1:=sFilters(x)(1), _
                        Operator:=sFilters(x)(3), Criteria2:=sFilters(x)(2)
            End Select
        Next x
    End With
    
End Sub

Any help why it's doing this will be most welcome.

Regards,
Darren.

 

[Rijnsent]

Hi Darren,

it does sound like the Range going into the function is one row short or has headers where it shouldn't have? Have you checked Debug.Print rDataRange.Address ? I ran your code on the bit below, my headers are in row 1, and it works like a charm.

Regards,

Koen

Sub GoFilter()

Dim rMainData As Range
Set rMainData = Worksheets("BTC").Range("B1:E43")
SetFilter rMainData, Array(1, "=kraken"), Array(2, ">0")
Set rMainData = Nothing

End Sub

 

[Darren Bartrup]

Hi Koen,

I've had a look at the data range and it's covering the table including headers and extra rows at the bottom.

I have found that if I filter it so that Critieria1:="=10/02/2014" I get everything except the last two rows, but if I filter Critieria1:="10/02/2014" I only get the last two rows and that's without the procedure - just hardcoded it into the Immediate Window.

If I can't find a resolution I'm hoping it's because I'm running Excel 2003 (11.5612.5606) which looks like it's not had any service packs added or been updated at all since Excel 2003 was first released and then I can point out they need to upgrade from an abacus.

I've run the below code which returns everything except the last two rows.

Sub FilterTest()
    Dim rMainData As Range
    Set rMainData = Worksheets("Log").Range("A1:P5961")
    SetFilter rMainData, Array(7, "=" & DateValue(Date)), Array(13, "=")
    Set rMainData = Nothing
End Sub

And this only returns the last two rows. All I've changed is the "=" before the datevalue.

 Sub FilterTest()
    Dim rMainData As Range
    Set rMainData = Worksheets("Log").Range("A1:P5961")
    SetFilter rMainData, Array(7, DateValue(Date)), Array(13, "=")
    Set rMainData = Nothing
End Sub

 

[Rijnsent]

Hi Darren,

mmm, I did test your code on Office 2013... Are those two last rows dates (not text values with e.g. a ' in front of them)? And would you happen to have the file somewhere (dropbox/skydrive/google drive)? I'd happily give it a spin on Office 2010/2013 to see if it does work on that version?

Regards,

Koen

 

[Darren Bartrup]

Hi Koen,

Apologies for leaving it a while - birthdays, days off, etc.

I've managed to get it working now by using a different approach.
Column A had unique reference numbers, which I can use to filter to my criteria (basically new reference numbers) and it all works - so I'm guessing that the problem lies in Excel translating the dates for filtering, which I've head elsewhere.

All good now though and ready to be used in a live environment on Monday (wish I had more time to test it though).