VBA code for deleting rows that contains #VALUE!

[tommybone30]

Hello,

I need help with providing the right code.

After doing one Macro automation, I would like to delete any rows that have a cell value of "#VALUE!" (which is essentially a computed formula that comes back with that message). Is it possible for it to be done? And keep in mind that this needs to be done on multiple worksheets, so the code needs to be applied on each sheet (if the "#VALUE!" message exists).

Please help!

Thank you!

 

[tommybone30]

Here is another macro that you can consider...

Sub DelErr()
  Columns("I").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub

Quick question Rick.

How do I write in something similar to your code, but to only those cells that display a certain answer.

For example, I want to delete entire row that has the cells containing "Temp-HD".

 

[tommybone30]

Assumes data begins in Column A

Option Explicit


Sub DelNA()
    Dim lr As Long, i As Long
    lr = Range("c" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If IsError(Range("C" & i)) Then
            If Range("A" & i) = "" Then
                Range("A" & i).EntireRow.Delete
            End If
        End If
    Next i
End Sub

I think the issue I have with this is that the computation takes a REALLY long time to compute. Is there a coding structure that doesn't take so much processing power to compute?

 

[Rick Rothstein]

Quick question Rick.

How do I write in something similar to your code, but to only those cells that display a certain answer.

For example, I want to delete entire row that has the cells containing "Temp-HD".

Assuming we are still talking about Column I...

[table="width: 500"]
[tr]
	[td]Sub DelErr()
  Columns("I").Replace "Temp-HD", "#N/A", xlPart, , , , False, False
  Columns("I").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]