cannot get it to auto filter

zakizelani

New Member
Joined
Mar 3, 2016
Messages
25
i cant seem to find whats my mistake from the code. when i do manually filter i can get the table to filter the data base on my criteria. howver when i run my vba code, it cannot filter the data with the criteria.




Code:
Sub CopyFilter()'clear the contents
    Sheet12.Range("B4:P10000").ClearContents
'copy and paste the range
    Sheet7.Range("Database").SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheet12.Range("B4")
End Sub




Code:
Sub Between2Dates()'declare the variables
Dim DateBegin As Date
Dim DateEnd As Date
Dim Rng As Range
'set error handler
'On Error GoTo errHandler:
'stop screen flicker
Application.ScreenUpdating = False
'set the variables
Set Rng = Sheet7.Range("B8")
DateBegin = Format(Sheet12.Range("B2").Value, "mm/dd/yy")
DateEnd = Format(Sheet12.Range("C2").Value, "mm/dd/yy")
'check the dates if all is OK run the filter
If Sheet12.Range("B2").Value >= Sheet12.Range("C2").Value Then
MsgBox " Your start value is wrong"
Exit Sub
Else
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then
'run the filter
With Rng
.AutoFilter Field:=3, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd


                                                                            
'copy values
CopyFilter
'show all data
Showall
End With
End If
End If
'error block
'On Error GoTo 0
Exit Sub


'errHandler:
MsgBox "There is no data"
Showall




End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
If you stop the code at that point, is there any filter applied to that column?
 
Upvote 0
BTW, this will always be True:

Code:
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then

Only a Variant can be Empty -- not a Date or any other simple variable.
 
Upvote 0
If you stop the code at that point, is there any filter applied to that column?


yes, they filter it but there are no data even though it meets both the criteria which fall between the both dates. however, when i manually do, the dates at the filter data is correct but it seems like the code just didnt click ok because when i click okay there is data that pops out
 
Upvote 0
BTW, this will always be True:

Code:
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then

Only a Variant can be Empty -- not a Date or any other simple variable.

so you think i should just delete that line???? hahahhaha, because i just follow the video from youtube. im new to vba btw
 
Upvote 0
Code:
If DateBegin) <> 0 And DateEnd <> 0 Then
 
Upvote 0
My question is, when you look at the filter, it it exactly the same as when you apply the filter manually?

And when you filter the data manually, it works? (to verity it's not your dates causing problems)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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