Macro that deletes a row based on multiple conditions

[deja2000]

Please help me : pray2: I have been stuck on two macros for hours and just can't get it right! I'm working on a spreadsheet that has 15,000 rows of data. Approximately 1/3 of the rows need to be deleted. I've tried a few different macros but none of them are working. Either all the rows are not taken into account or nothing at all happens. I need help on two counts:


I would like to delete rows if the value of a cell in column "a" is not a date. Where I keep getting stuck is having the condition be any date not a specific date and for the macro not to include rows 1-5 which is the header for the report.
Secondly I need to delete rows if the entire row is blank or if a cell in column "p" is blank, again not taking into account the header cells.</IMG>

[georgiboy]

Hi, maybe something like this...
Sub DelRow()

Dim endRow As Long, x As Long

endRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

For x = endRow To 6 Step -1
If IsDate(Sheet1.Range("A" & x).Value) = False Or Sheet1.Range("P" & x).Value = "" Then
Sheet1.Rows(x).EntireRow.Delete
End If
Next x

End Sub
Hope this helps.

[deja2000]

Thank you, I'm about to try to code now. I love your saying at the bottom of your post about the impossible...is that yours?

[deja2000]

I tried the code but it keeps deleting everything below row 6.

[deja2000]

I got it to work by making the following updates to the spreadsheet prior to running the code:

Un-merged the cells in column "A".
All cells in column "A" are formatted as "custom" when the spreadsheet is created by the exported report. I changed the formatting of column "A" to "date".
I filtered column "A" so that none of the cells with dates shown. I then formatted all of those cells to "text".Thank you so much for your help:bow:! We are a small company and don't usually keep a developer on staff, but if any contract work comes up I will keep you mind!

[jackdandcoke]

I'm trying to piggyback off of this. I need to delete every row from 18 to 164 where P and Q are empty. I tried this:

Sub CleanUp()

Dim endrow As Long
Dim x As Long

endrow = Sheets("Work Order").Range("A" & Rows.Count).End(x1Up).Row

For x = endrow To 6 Step -1
If Sheets("Work Order").Range("P" & x).Value = "" And Sheets("Work Order").Range("Q" & x).Value = "" Then Sheets("Work Order").Rows(x).EntireRow.Delete
End If
Next x

End Sub

It's telling me it expects a GOTO.

Edit: Found the error but it says that I need to "End If without Block If"

[CatDaddy]

if ....... then
do stuff
end if

[jackdandcoke]

looking for the If Part of that statement....

[CatDaddy]

For x = endrow To 6 Step -1
If Sheets("Work Order").Range("P" & x).Value = "" And Sheets("Work Order").Range("Q" & x).Value = "" Then
Sheets("Work Order").Rows(x).EntireRow.Delete
End If
Next x

[jackdandcoke]

Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.


Dim endrow As Long
Dim x As Long

For x = endrow To 29 Step -1
If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then
Sheets("Sheet1").Rows(x).EntireRow.Delete
End If

Next x

Why won't it remove the rows?