VBA to delete multiple rows based on multiple criteria?

[jsabo]

Hello,

I have searched around for similar code to use, but have not been able to make it work for what i am doing. I need to run the script and have it delete entire rows based on two different columns. Let's say column G is "Scheduled submittal date" and column L is "document status". I want to delete every row where column G has anything other than nulldate and where Doc Status has anything other than <blank>. Basically, what remains should be "nulldate" in column G and <blank> in column L.

Any suggestions?

Thanks!

[enrand22]

what about something like this.... if you wonder why i use clearcontents instead of delete it is becuase for each becomes crazy.

Sub test()
Dim bcell As Range
For Each bcell In Range("g1:g" & Range("a1048576").End(xlUp).Row)
If bcell.Value <> "nulldate" Then
bcell.Row.ClearContents
ElseIf bcell.Offset(0, 6).Value <> "" Then
bcell.Row.ClearContents
End If
Next bcell
End Sub


may be at the end you only need to reorder your database to get rid of the blanks.

[dannohayes]

Here's how I would do it:

Public Sub DelLines()
Dim currow As Long

currow = 1
Do
currow = currow + 1
If Trim(ActiveSheet.Cells(currow, 1).Value) = "" Then
Exit Do 'exit if past last data row
End If

If Trim(ActiveSheet.Cells(currow, 7).Value) <> "" And _
Trim(ActiveSheet.Cells(currow, 12).Value) <> "" Then
ActiveSheet.Cells(currow, 1).EntireRow.Delete 'delete row
currow = currow - 1 'adjust row marker back to account for deleted row
End If
Loop


End Sub

I'm assuming a contiguous data table here

[jsabo]

Here's how I would do it:

Public Sub DelLines()
Dim currow As Long

currow = 1
Do
currow = currow + 1
If Trim(ActiveSheet.Cells(currow, 1).Value) = "" Then
Exit Do 'exit if past last data row
End If

If Trim(ActiveSheet.Cells(currow, 7).Value) <> "" And _
Trim(ActiveSheet.Cells(currow, 12).Value) <> "" Then
ActiveSheet.Cells(currow, 1).EntireRow.Delete 'delete row
currow = currow - 1 'adjust row marker back to account for deleted row
End If
Loop


End Sub

I'm assuming a contiguous data table here

Thanks, this almost works. does not get rid of rows that have a date in scheduled submittal date... can you amend it to include those deletions? thanks in advance to both you.

[SamT]

In the second If statement, change the "And" to "Or."

[jsabo]

In the second If statement, change the "And" to "Or."
I did that and it deleted absolutely every column...

[SamT]

Change the test on G to read:

If ActiveSheet.Cells(currow, 7).Value <> "nulldate" And _

[dannohayes]

Changing the 'And' to 'Or' worked on mine. Can you give us an example of your data table?