Macro to Delete Row IF

**Andrew.Trevayne** · 10-12-2011, 11:25 PM

Hi everyone,
Any suggestions on how to delete rows by VBA?
The condition is, If the value on Cell (From D7 to last record) is equal to "#VALUE!"
It will delete the entire row.
Appreciate your help once more. Thank you in advance.

Andrew

**venkat1926** · 10-13-2011, 02:08 AM

when you insert or delete rows start from last row to first row.

as you are tampering with your data sheet better copy sheet1 to sheet 2 as precaution

try this macro

Sub test()
Dim j As Long
Worksheets("sheet1").Activate
For j = Range("D1").End(xlDown).Row To 1 Step -1
If Cells(j, "D") = "True" Then Cells(j, "D").EntireRow.Delete
Next j
End Sub

**arlu1201** · 10-13-2011, 02:26 AM

Venkat,

I do not see the #value condition in the code. The rows have to be deleted based on that condition.

**Andrew.Trevayne** · 10-13-2011, 08:47 PM

Hi Venkat,
Tried the Script but it doesn't do anything on my spreadsheet. Please advise.
Thank you.

**jaslake** · 10-13-2011, 10:27 PM

Hi Andrew.Trevayne

Slight modification of venkat1926 code...see if this works

Option Explicit
Sub test()
    Dim j As Long
    Worksheets("sheet1").Activate 'set this to your sheet
    For j = Range("D7").End(xlDown).Row To 1 Step -1
        If Cells(j, "D").Text = "#VALUE!" Then Cells(j, "D").EntireRow.Delete
    Next j
End Sub