Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete Rows by Color in Excel From Access Using Autofilter 1

Status
Not open for further replies.

sxschech

Technical User
Jul 11, 2002
1,033
US
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

 
hi,

Give this a try...
Code:
'
    With ws
        lastRow = .Range("A1").CurrentRegion.Rows.Count
        lastCol = .Range("A1").CurrentRegion.Columns.Count
        .AutoFilterMode = False
        With .Range("A1:A" & lastRow)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
        End With
        .Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Hey Skip,

Thanks for taking a look at this. I tried your suggestion and it still halts with error messages on these two lines mentioned in original post.

'This line shows Run-time error 1004 AutoFilter method of Range class failed
.AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor

'This line shows Run-time error 1004 Unable to get the SpecialCells property of the Range class
.Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 
Do you have a reference set to the Microsoft Excel Object Library?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
That did it. I was using the late binding method so that I didn't need to worry about references as when I worked on various computers and gave the code to the users, always seemed to be an issue, 2007, 2010, 2013. So I guess I'll have to go back to dealing with potential broken refs.

Thanks.
 
xlCellTypeVisible, for instance, is a constant. If you use the actual value then late binding is not a problem.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thanks for your guidance. Changing to constant and a few other tweaks got it to work with late binding. Here is the revised code block:

Code:
With ws
   lastrow = .Range("A1").currentregion.Rows.Count
   lastCol = .Range("A1").currentregion.Columns.Count
   .autofiltermode = False
   .Range("A1:A" & lastrow).AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=9    'xlFilterFontColor Constant=9
   .Range("A2:A" & lastrow).Specialcells(12).entirerow.Delete      'xlcelltypevisible Constant=12
   .autofiltermode = False
End With
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top