PDA

View Full Version : [SOLVED] Filter between two dates on multiple ranges



Maxicus
05-03-2018, 11:03 PM
Hi everyone who is reading this.

Hope someone can help me on this, I am trying to filter data between two dates, I have it working well, however as soon as i try to get it to filter the data on multiple ranges it gives my a run-time error 1004 autofilter on range class failed. and it also does not copy the filtered data.

This is what the code looks like:


Private Sub CommandButton3_Click()
'declare the variables
Dim DateBegin
Dim DateEnd
Dim Rng As Range

'stop screen flicker
Application.ScreenUpdating = False

'set the variables
Set Rng = Sheet2.Range("A1")
DateBegin = Format(Sheet6.Range("C2").Value, "Long Date")
DateEnd = Format(Sheet6.Range("E2").Value, "Long Date")

'check the dates if all is OK run the filter
If Sheet6.Range("C2").Value >= Sheet6.Range("E2").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:=30 Or 31 Or 32, 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"
ShowallSheet1.Select
End Sub

the error occurs at this line:

'run the filter
With Rng
.AutoFilter Field:=30 Or 31 Or 32, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd

If I remove "or 32" I don't get any error but it also doesn't filter the data or copy it.
If I remove "or 31 or 32" The code runs perfect but only filters the data in column 30. I am trying to get it to filter all the data in multiple columns.

Fluff
05-04-2018, 08:58 AM
Hi, try
With Rng
.AutoFilter Field:=30, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
.AutoFilter Field:=31, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
.AutoFilter Field:=32, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd

mana
05-04-2018, 10:42 PM
Option Explicit


Sub test()
Dim DateBegin As Long
Dim DateEnd As Long
Dim r As Range
Dim c As Range

DateBegin = Sheet6.Range("C2").Value2
DateEnd = Sheet6.Range("E2").Value2

Set r = Sheet2.Range("A1").CurrentRegion
Set c = r.Offset(r.Rows.Count + 2).Resize(4, 1)
c(2).Formula = "=AND(AD2>=" & DateBegin & ",AD2<=" & DateEnd & ")"
c(3).Formula = "=AND(AE2>=" & DateBegin & ",AE2<=" & DateEnd & ")"
c(4).Formula = "=AND(AF2>=" & DateBegin & ",AF2<=" & DateEnd & ")"

r.AdvancedFilter xlFilterInPlace, c

c.ClearContents

End Sub