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.
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.