I would like to find out if it is possible to delete rows in excel from access without having to loop through each cell as is the present situation because seems that it would be faster. I recorded the macro and copied the code into access, but it gets stuck on the autofilter line as well as the specialcells line. After searching through various sites and trying suggestions, didn't have much luck, however, seems that the autofilter itself is activated if it doesn't have criteria.
Code:
Set objapp = CreateObject("Excel.Application")
objapp.Visible = True
Set wb = objapp.workbooks.Open(FileName, True, False)
Set ws = wb.worksheets("Siblings")
'select all worksheets & cells In turn
'For Each ws In wb.worksheets
'amend font To whatever
ws.Activate
With ws
lastrow = .Range("A1").currentregion.Rows.Count
lastCol = .Range("A1").currentregion.Columns.Count
.autofiltermode = False
.Range("A1:A" & lastrow).Select
'This line works
.Range("A1:A" & lastrow).AutoFilter
'This line shows Run-time error 1004 AutoFilter method of Range class failed
.Range("A1:A" & lastrow).AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
.Range("A2:A" & lastrow).Select
'This line shows Run-time error 438 Object doesn't support this property or method
.Selection.Specialcells(xlcelltypevisible).entirerow.Delete
End With