filter..Month in VBA

[boxboy30]

So I'm stuck (again!)...

How do I create a form that would ask the user what month they would like to filter then execute the .autofilter in VBA? the column currently has dates in format

<TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=118><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 width=118 align=right>mm/dd/year time
</TD></TR></TBODY></TABLE>

 

[John_w]

Suppose the dates are in column A on Sheet1 and the user form has a text box (TextBox1) for the user to enter the month number and a command button (CommandButton1) which when clicked filters the data for the specified month (and current year), try this:

UserForm1 code

Private Sub CommandButton1_Click()

    If Me.TextBox1.Value <> "" Then
        AutoFilter_By_Month Me.TextBox1.Value
    Else
        MsgBox "Enter month number (1-12) in text box"
    End If
    
End Sub


Sub AutoFilter_By_Month(month As Integer)

    'AutoFilter date criteria must be declared as Long (not Date) otherwise AutoFilter doesn't work
    
    Dim firstDateOfMonth As Long, lastDateOfMonth As Long
    
    'First date of specified month in current year
    
    firstDateOfMonth = DateSerial(Year(Date), month, 1)
    
    'Last date of specified month in current year
    
    lastDateOfMonth = DateSerial(Year(Date), month + 1, 0)
        
    With Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1").Select             'Select a cell within the data to be autofiltered
    End With
    Selection.AutoFilter Field:=1, Criteria1:=">=" & firstDateOfMonth, Operator:=xlAnd, Criteria2:="<=" & lastDateOfMonth

End Sub

 

[boxboy30]

ahhh...I'm trying it now.

 

[boxboy30]

I'm getting the current error message on:

If Me.TextBox1

invalid use of Me keyword...

and the data is in column "D"....

 

[John_w]

I'm getting the current error message on:

If Me.TextBox1

invalid use of Me keyword...

and the data is in column "D"....

I don't get that error. Have you created a user form as I described?

With the date-times in column D instead of A, change:

.Range("A1").Select 'Select a cell within the data to be autofiltered

to:

.Range("D1").Select 'Select a cell within the data to be autofiltered

Post your code if it's still not working.

 

[boxboy30]

Will do...no I didn't create a user form...I'm sure that's what the problem was.

Thanks for your help! I love this site! How does one donate?

 

[boxboy30]

Okay...I've created a userform and inserted it into my code, HOWEVER, once I press "continue" it erases all data:

complete code as follows:

Range("C:C,E:E,G:G,I:I,J:J,L:L,M:M,N:N,O:O,P:P,R:R,S:S").Delete Shift:=xlToLeft
Columns("D:D").Select
ActiveWorkbook.Worksheets("x").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("x").Sort.SortFields.Add Key:= _
Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("x").Sort
.SetRange Range("A2:G109585")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
ActiveSheet.Range("$D$1:$D$109585").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/24/2010", 0, "12/25/2009", 0, "12/25/2008" _
)
Range("A2:G68131").Select
ActiveWindow.SmallScroll Down:=6
Selection.EntireRow.Delete
Selection.AutoFilter
Columns("B:B").Select
ActiveWorkbook.Worksheets("x").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("x").Sort.SortFields.Add Key:= _
Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("x").Sort
.SetRange Range("A2:G109585")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:G").Select
Selection.ColumnWidth = 19

MonthForm.Show

(MonthForm Code =)

Private Sub CommandButton1_Click()
If Me.TextBox1.Value <> "" Then
AutoFilter_By_Month Me.TextBox1.Value
Else
MsgBox "Please Enter Month"
End If
End Sub

Sub AutoFilter_By_Month(Month As Integer)

Dim firstDateOfMonth As Long
Dim lastDateOfMonth As Long

firstDateOfMonth = DateSerial(Year(Date), Month, 1)
lastDateOfMonth = DateSerial(Year(Date), Month + 1, 0)
With Sheets("x")
.AutoFilterMode = False
.Range("D1").Select
End With
Selection.AutoFilter Field:=1, Criteria1:=">=" & firstDateOfMonth, Operator:=xlAnd, Criteria2:="<=" & lastDateOfMonth
End Sub

End Sub

Not sure what's going on, but everything works until I imput 1 for January and press continue.

 

[John_w]

Okay...I've created a userform and inserted it into my code, HOWEVER, once I press "continue" it erases all data:

complete code as follows:

Range("C:C,E:E,G:G,I:I,J:J,L:L,M:M,N:N,O:O,P:P,R:R,S:S").Delete Shift:=xlToLeft

My code doesn't delete any data, it just autofilters on column D. Maybe the line above is the problem. You say that column D contains the dates, but which other columns are populated?

I suggest you start with my code in a new workbook, with column D containing dates and column E onwards containing other data, add a user form as described and see how the code works and then integrate it into your code.

 

[boxboy30]

Okay...I'll play around with it.