Solved: Delete Rows if cells in columns are empty

[john3j]

Hello,

I have a worksheet that imports a table from access and have conditional fomatting that Identifies whether items are late are not. If it is late to department1, it turns the cell in AC red. If it is late to department1, it turns the cell red in AD. I would like the code to find the last row in the workbook, which there will always be something in column D...so you could base the last row off of that column. Anyways, I want each row that doesnt have coloring in columns AC and AD to be deleted from the workbook. I only want Rows that have columns that are red in AC and AD, or both at the same time. Any help would be appreciated.

Thanks,
John

[mbarron]

You need a macro that will test the two conditions that cause the color changes, and if both are false, then delete the row.

Something like the following. It assumes column 1 has data to the end of the table. It then loops the table and checks each row to see if the value in column 2 is greater than the value in column 3 and if the value in column 4 is greater than the value in column 5. If both conditions are true, the row is deleted.

[vba]Sub deleter()

Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = lRow To 2 Step -1
If Cells(i, 2) > Cells(i, 3) And Cells(i, 4) > Cells(i, 5) Then
Cells(i, 1).EntireRow.Delete
End If
Next

End Sub[/vba]

[Simon Lloyd]

It's a little more difficult than that as you have to test for the conditional formatting and evaluate the formula.

Can you provide a sample workbook with the conditional formatting in place?

[mbarron]

I.... you have to test for the conditional formatting and evaluate the formula.

That's what this statement was intended to convey " You need a macro that will test the two conditions that cause the color changes"

[john3j]

Hello,

I have attached a sample workbook so that you can see what Im working with here. Any help would be appreciated. I scrubbed the document to protect myself. If you have any other questions or comments, let me know.

Thanks,
John

[mbarron]

I believe this one should do it:
[VBA]
Sub deleter()
Application.ScreenUpdating = False
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = lRow To 2 Step -1
If Not ((Cells(i, 18) <> "" And Cells(i, 18) < Date And Cells(i, 19) = "" And Cells(i, 21) = "") Or _
(Cells(i, 22) = "" And Cells(i, 20) < Date And Cells(i, 20) <> "")) Then
Cells(i, 1).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub

[/VBA]

[geekgirlau]

Hi John,

How are you getting the data from Access? Can I suggest that you setup a query in Access and import that rather than the whole table? That way you're only importing the records that you actually want, instead of importing everything and deleting the records you don't want.