CODEWITHSUNDEEP
excelvba
mr. lumpy
mr. lumpy

Reputation: 1

VBA Filtering Year

I have the code shown below. The first time I filter, it works fine. However the second time, it does not. The filtered column contains years.

For i = 1 To LastRow
    If wSheet.Range("A1").Offset(i, 0) <> wSheet.Range("A1").Offset(i + 1, 0) Then
        WellName = wSheet.Range("A1").Offset(i, 0)
        Set rng = wSheet.Range("A1", Cells(LastRow, LastColumn))
        rng.AutoFilter Field:=1, Criteria1:=Name
        L_top = wSheet.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Row
        L_bot = wSheet.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count
        LastRowFilter = wSheet.Range("A1").End(xlDown).Row
        LastColumnFilter = wSheet.UsedRange.Columns.Count

        For j = 1 To LastRowFilter
            If Year(wSheet.Range("B1").Offset(j, 0)) <> Year(wSheet.Range("B1").Offset(j + 1, 0)) Then
                ReportYear = Year(wSheet.Range("B1").Offset(j, 0))
                Set rng2 = wSheet.Range("B1", Cells(LastRowFilter, LastColumnFilter))
                rng2.AutoFilter Field:=2, Criteria2:=ReportYear
                L_top = wSheet.Range("B2:B" & LastRowFilter).SpecialCells(xlCellTypeVisible).Row
                L_bot = wSheet.Range("B2:B" & LastRowFilter).SpecialCells(xlCellTypeVisible).Count

            End If
        Next

    End If
 Next

Upvotes: 0

Views: 3553

Answers (1)

RocketDonkey
RocketDonkey

Reputation: 37249

It appears that your issue is your second filter (it seems like the initial filter should cause and error, but if it ain't broke don't fix it :) ). The problem is with the following lines:

ReportYear = Year(wSheet.Range("B1").Offset(j, 0))
Set rng2 = wSheet.Range("B1", Cells(LastRowFilter, LastColumnFilter))
rng2.AutoFilter Field:=2, Criteria2:=ReportYear

You are setting ReportYear equal to the year you want to filter on (makes sense), but then you are trying to filter on values that equal that year. This seems to make sense logically, but remember that the column contains dates, not years. Not knowing anything else about your code (so apologies for any other errors), this seems to work on my simple tests:

ReportYear = Year(wSheet.Range("B1").Offset(j, 0))
Set rng2 = wSheet.Range("B1", Cells(LastRowFilter, LastColumnFilter))
rng2.AutoFilter Field:=2, Criteria1:= ">=" & DateSerial(ReportYear, 1, 1), _
               Operator:=xlAnd, Criteria2:="<=" & DateSerial(ReportYear, 12, 31)

This uses your ReportYear value but turns the filter into the form "Show me all dates between January 1 and December 31 of ReportYear". I agree that this seems a bit inelegant (I'm sure one of the gurus here has a much shorter solution), but it may work in your situation.

Upvotes: 1

Related Questions