Filter:=2 Giving Compile Error "Expected End of Statement"

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
748
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
VBA Code:
Public Sub MyFilter()
    Dim lngStart As Long, lngEnd As Long
    Dim Rng As Range
    Application.ScreenUpdating = False
    Worksheets("Data").Activate
    lngStart = Range("R1").Value
    lngEnd = Range("R2").Value
    Worksheets("R_Data").Activate
    Range("B2:B" & Range("B2").End(xlDown).Row).NumberFormat = "mm/dd/yyyy"
    Set Rng = Range("B2:AA" & Range("B2").End(xlDown).Row).AutoFilter field:=2, Criteria1:"=>"&lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
    Worksheets("Data").Activate
    Rng.Copy
    Range("AA1").PasteSpecial xlPasteAll
End Sub

I am getting an error in the following line
Set Rng = Range("B2:AA" & Range("B2").End(xlDown).Row).AutoFilter field:=2, Criteria1:"=>"&lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
specifically it is not accepting field:=2

I am using Excel Version 2019. I could not figure out the mistake. Your help on this is much appreicated.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Can you explain in words exactly what you have and what you are trying to do?
 
Upvote 0
Thanks Peter_SSs for your valuable time. Here I will explain.

1. I have a range of data in Sheet "R_Data" from B1 to AA1000. In this Column B is having a date value in text format.
2. I want to pick the data from the above range where the column B falls between two dates which is stored Sheet "Data" Cell R1 and R2
3. Copy the picked-up data into the Sheet "Data" in the cell AA1
 
Upvote 0
VBA Code:
Public Sub MyFilter()
    Dim lngStart As Long, lngEnd As Long
    Dim Rng As Range
    Dim iCol As Long
    Application.ScreenUpdating = False
    iCol = 2
    Worksheets("Data").Activate
    lngStart = Range("R1").Value 
    lngEnd = Range("R2").Value
    Worksheets("R_Data").Activate
    ActiveSheet.Range("A1:Q1000").AutoFilter iCol, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:=">=" & lngEnd
    Set Rng = ActiveSheet.Range("A1:Q" & ActiveSheet.Range("A1").End(xlDown).Row)
    Worksheets("Data").Activate
    Rng.Copy
    Range("AA1").PasteSpecial xlPasteAll
End Sub

The above code is working for my requirement. It is not taking it as Filter:= but a variable. I found this workaround in an another thread in this forum. This forum is an excellent place to get help on anything in excel for people like me. Thank you so much guys.
 
Upvote 0
I see that you have posted while I was composing this but I was close to finished when I noticed that so I will post this anyway. It is based on posts 1 to 3 only.

I'm not sure if I have set up exactly as you have but a few points
  • You do not need to activate the sheets to work with them and activating slows your code somewhat
  • You cannot set the range and filter it at the same time as you have tried to do.
  • When filtering, you need to include the headers which I assume are in row 1
  • Your range starts in column B so your autofilter should be Field:=1 not 2
  • Your autofilter syntax is also wrong in that it is missing an "=" sign as shown here
    1628410250545.png
  • You may want to unfilter the range after the copy

Based on all the above, perhaps something like this

VBA Code:
Public Sub MyFilter_v2()
    Dim lngStart As Long, lngEnd As Long
    Dim Rng As Range
    Application.ScreenUpdating = False
    With Worksheets("Data")
      lngStart = .Range("R1").Value
      lngEnd = .Range("R2").Value
    End With
    With Worksheets("R_Data")
      .Range("B2:B" & .Range("B2").End(xlDown).Row).NumberFormat = "mm/dd/yyyy"
      Set Rng = .Range("B1:AA" & .Range("B2").End(xlDown).Row)
      Rng.AutoFilter field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
      Rng.Offset(1).Resize(Rng.Rows.Count - 1).Copy
      Worksheets("Data").Range("AA1").PasteSpecial xlPasteAll
      .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub
 
Upvote 0
I see that you have posted while I was composing this but I was close to finished when I noticed that so I will post this anyway. It is based on posts 1 to 3 only.

I'm not sure if I have set up exactly as you have but a few points
  • You do not need to activate the sheets to work with them and activating slows your code somewhat
  • You cannot set the range and filter it at the same time as you have tried to do.
  • When filtering, you need to include the headers which I assume are in row 1
  • Your range starts in column B so your autofilter should be Field:=1 not 2
  • Your autofilter syntax is also wrong in that it is missing an "=" sign as shown hereView attachment 44374
  • You may want to unfilter the range after the copy

Based on all the above, perhaps something like this

VBA Code:
Public Sub MyFilter_v2()
    Dim lngStart As Long, lngEnd As Long
    Dim Rng As Range
    Application.ScreenUpdating = False
    With Worksheets("Data")
      lngStart = .Range("R1").Value
      lngEnd = .Range("R2").Value
    End With
    With Worksheets("R_Data")
      .Range("B2:B" & .Range("B2").End(xlDown).Row).NumberFormat = "mm/dd/yyyy"
      Set Rng = .Range("B1:AA" & .Range("B2").End(xlDown).Row)
      Rng.AutoFilter field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
      Rng.Offset(1).Resize(Rng.Rows.Count - 1).Copy
      Worksheets("Data").Range("AA1").PasteSpecial xlPasteAll
      .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub

Thank you so much Peter_SSs???. It is a foolproof code I believe. Yet another good learning from you. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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