|
Option Explicit
Sub test()
Dim cn As Object, rs As Object, sq$
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=NO';Data Source=" & ThisWorkbook.FullName
sq = "SELECT * FROM [$a2:e" & Cells(Rows.Count, 1).End(xlUp).Row & "] WHERE LEFT(f1,1) NOT IN('4','8','9')"
rs.Open sq, cn, 1, 3
[a2].CopyFromRecordset rs
Range(Cells(rs.RecordCount + 2, 1), Cells(Rows.Count, 5)).ClearContents
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub |
|