Macro to Delete rows where Text Not TRUE in Col D

[howard]

I have True or False in Col E

i have tried to write code to delete these rows and if there are any blank rows to delete these as well

I get a run time error "autofilter method of range class failed"

It would be appreciated if someone could mend my code


Sub DeleteNotTrue()
With Sheets("check List")

With Cells(1).CurrentRegion
.AutoFilter 5, "<>TRUE"""
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With

End Sub [/code]

 

[vcoolio]

Hello Howard,

Changing the autofilter line a little should work:-

Sub DeleteNotTrue()

Application.ScreenUpdating = False
       
        With Sheets("Check List").Cells(1).CurrentRegion
                .AutoFilter 5, "<>" & "TRUE"
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

It should also take care of the blank rows assuming that the blank cells extend into Column E as well.

I hope that this helps.

Cheerio,
vcoolio.

P.S. Check your sheet name cases as well ("check list", "Check List"?)

 

[vcoolio]

Hello Howard,

I've just realised that your thread title says Column D. Your code says Column E. If need be, change the 5 to 4.

Cheerio,
vcoolio.

 

[howard]

Thanks for the help vcoolio

I have posted sample data on Dropbox -see link below

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I posted a similar type question on Excelforum and received a reply but did not work -Even though it was a different question to do with CF , I so dont want to violate any forum rules

Macro to clear CF in Col B and copy non CF items [SOLVED]

I have a check box that once ticked uses CF formatting to highlight the cells I need a macro to clear only the items highlighted by CF and then to move all the un-highlighted items in the blank cells above I have shown what the final result should look like on sheet "Check List after...

www.excelforum.com

 

[vcoolio]

Perhaps this Howard:-

Sub DeleteCFitems()

Application.ScreenUpdating = False
       
        With Sheet1.Range("E1", Sheet1.Range("E" & Sheet1.Rows.Count).End(xlUp))
                .AutoFilter 1, "<>" & "TRUE"
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

BTW, I wouldn't use check boxes in this type of situation as, once rows are deleted, embedded items (such as the check boxes) will just move up on top of each other as the rows move up on deletion.

Cheerio,
vcoolio.

 

[howard]

Thanks Vcoolio

Code works perfectly

 

[vcoolio]

You're welcome Howard. Glad to have been able to assist and thanks for the feed-back

Cheerio,
vcoolio.