Excel VBA Runtime Error 438 Autofilter Field number set as variable

[downunder80]

Hi,

Struggling with this error in line:
ActiveSheet.Selection.AutoFilter Field:=colcr, Criteria1:="1"

Background:
Excel version 2016
Range("RESULT") is always a cell in Row 1 but could be in any column.
In the live example Range("RESULT") is in Column 9 and the Locals window is populating colcr as 9 so all looks ok there.

Dim colcr As Long

colcr = Range("RESULT").Column

         If Range("RESULT").Value <> 0 Then
                Range(Range("A1"), "RESULT").Select
                Range(Selection, Selection.End(xlDown)).Select
                ActiveSheet.Selection.AutoFilter Field:=colcr, Criteria1:="1"
                Range(Range("A1"), "RESULT").Select
                Range(Selection, Selection.End(xlDown)).Copy
        End If

I'm not sure why I get this error and can't seem to fix it

Any help appreciated.

Thanks

 

[Osvaldo Palmeiro]

Hi. You could try this.

Sub Test()
 Dim colcr As Long
  colcr = Range("RESULT").Column
   If Range("RESULT").Value <> 0 Then
    Range("A1", "RESULT").AutoFilter Field:=colcr, Criteria1:=1
    Range("A1", Cells(Rows.Count, colcr).End(xlUp)).Copy
   End If
End Sub

 

[downunder80]

Hi Osvaldo, that worked for me.
Thank you!