VBA Auto Filter on Server Connection

[Radison32]

Hi All

I have created a VBA for filtering a table of values (see below) which works great, the reference used to filter the table is coming from another tab on the sheet.
When i try and apply the same type of VBA filter on data table on another tab which is a connection to a SQL server it fails
saying "Data-time error 1004".


Sub Filter()

With Sheets("Sheet2")

.Range("A2:H9").AutoFilter Field:=5, Criteria1:=Worksheets("Sheet3").Range("B2").Value
.Range("A2:H9").AutoFilter Field:=6, Criteria1:=Worksheets("Sheet3").Range("C2").Value


End With




End Sub

 

[Radison32]

Hi All

School boy error I selected more in the range than was being returned from the server.
An easy solution to my problem was to create a range that will grow as the results grow.

Sub Filter()

With Sheets("Sheet2")

Lastrow = ThisWorkbook.Sheets("Sheet2").Cells(2, 1).End(xlDown).Row
.Range("A2:A" & Lastrow).AutoFilter Field:=5, Criteria1:=Worksheets("Sheet3").Range("B2").Value

End With

End Sub

Thanks to anyone that looked in to this for me.