Limiting Autofilter Range within Column

[3link]

I have a column that is approximately 1331 rows long. Each cell in the column contains a formula that results in either " " (empty) or a date depending on input. In most cases, cells 700 and below will be " " (empty).

I have applied a vba auto filter macro to the columns to select specific date criteria. Every time I use the auto filter, all 1331 rows are filtered (including the empty rows). I'd like to limit the filter to the last filled row in the column.

Here's a sample of a formula I've tried.

        Case "This Week"

Dim dDate As Date
Lastrow = Range("B1331").End(xlUp).Row

If IsDate(Range("BM1331")) Then

    dDate = Range("BM1331")

    dDate = DateSerial(Year(dDate), Month(dDate), Day(dDate))
    
    Else

End If

lDate = dDate

Range("B15:B" & Lastrow).AutoFilter

Range("B15:B" & Lastrow).AutoFilter Field:=1, Criteria1:=">=" & lDate, Operator:=xlAnd, Criteria2:="<=" & lDate + 6

Case "This Month"

Dim eDate As Date

If IsDate(Range("BM1332")) Then

    eDate = Range("BM1332")

    eDate = DateSerial(Year(eDate), Month(eDate), Day(eDate))

End If

As you can see, I'm attempting to apply the filter to column B depending on the values of cells BM1331 and BM1332. The filter works fine, except it still filters all 1331 cells of column B. Any suggestions?

 

[Jerry Sullivan]

Each cell in the column contains a formula that results in either " " (empty) or a date depending on input. In most cases, cells 700 and below will be " " (empty).

The problem is that Excel doesn't consider " " empty. Your code should work if you make both of these changes:

Change your formula to result in a null string "" (no space)
And
Change your lastrow code to:

lastrow = Columns("B").Find(What:="*", After:=Range("B1"), LookIn:=xlValues, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row