VBA code for deleting rows that contains #VALUE!

T

tommybone30

New Member
Joined
Dec 14, 2017
Messages
14
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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Can the error message #value appear in any cell in the worksheet or is it in a particular column. If in a particular column, which one.
 
Upvote 0
Code: 
Option Explicit


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

Use the above code to do each sheet separately. If you wish to loop through the sheets then use the code below.
Code: 
Option Explicit


Sub DelErr()
    Dim lr As Long, i As Long
    Dim w As Worksheet
    For Each w In Worksheets
    lr = w.Range("I" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If IsError(w.Range("I" & i)) Then
            w.Range("I" & i).EntireRow.Delete
        End If
    Next i
End Sub
 
Last edited:
Upvote 0
Here is another macro that you can consider...
Code: 
Sub DelErr()
  Columns("I").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub
 
Upvote 0
alansidman said:
Code: 
Option Explicit


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

Use the above code to do each sheet separately. If you wish to loop through the sheets then use the code below.
Code: 
Option Explicit


Sub DelErr()
    Dim lr As Long, i As Long
    Dim w As Worksheet
    For Each w In Worksheets
    lr = w.Range("I" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If IsError(w.Range("I" & i)) Then
            w.Range("I" & i).EntireRow.Delete
        End If
    Next i
End Sub
Click to expand...

I kept getting "Compile error: For without Next". Any reasons why?
 
Upvote 0
Yup. Missed a next command. After the Next i, insert on the next line,
Code: 
Next w
 
Upvote 0
Awesome, it works!

However, I have a follow-up question. It is very similar to the one above.

In another Excel project, there are usually monthly benefits data that are filled out within multiple columns. In my coding, I ask it to create a new column (see yellow highlight column below), and fill out a Vlookup formula (using the SSN as my lookup value). However, the amount of people in the file changes month to month, and since I do not know what the limit was, I simply ask the Vlookup formula to go two times above the amount of data (e.g. there's roughly 600 people average per month, but I ask Vlookup to look for 2000 cells because you would never know if there are WAY more people the next month that could go way above 600).

Now, obviously the cells that does not have any corresponding data in the same row will issue a #N/A error. However, due to the nature of the Vlookup, it's possible that a few rows that DOES contain corresponding data will also get the #N/A error, which I do not want to delete (I will simply make manual changes to get the row's data accurate).

For visual aid:

[TABLE="width: 994"]
<colgroup><col width="215" style="width: 161pt; mso-width-source: userset; mso-width-alt: 7862;" span="2"> <col width="172" style="width: 129pt; mso-width-source: userset; mso-width-alt: 6290;"> <col width="172" style="width: 129pt; mso-width-source: userset; mso-width-alt: 6290;" span="2"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="126" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4608;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <tbody>[TR]
[TD="width: 215, bgcolor: transparent, align: right"][/TD]
[TD="width: 215, bgcolor: transparent"] xxx-xx-0833[/TD]
[TD="width: 172, bgcolor: transparent"]THOMAS, MICHAEL[/TD]
[TD="class: xl63, width: 172, bgcolor: yellow"]3170[/TD]
[TD="class: xl63, width: 172, bgcolor: yellow"]COI[/TD]
[TD="width: 113, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"]Cancer[/TD]
[TD="width: 126, bgcolor: transparent, align: right"]24.9[/TD]
[TD="width: 66, bgcolor: transparent, align: right"]24.9[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"] [/TD]
[TD="bgcolor: transparent"] xxx-xx-0191[/TD]
[TD="bgcolor: transparent"]THOMAS, SCOTT[/TD]
[TD="class: xl63, bgcolor: yellow"]3225[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]26.33[/TD]
[TD="bgcolor: transparent, align: right"]26.33[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-9027[/TD]
[TD="bgcolor: transparent"]THORNTON, TATIANA[/TD]
[TD="class: xl63, bgcolor: yellow"]3160[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-4156[/TD]
[TD="bgcolor: transparent"]TUCKER, JHAN[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-9327[/TD]
[TD="bgcolor: transparent"]UN, AMANDA[/TD]
[TD="class: xl63, bgcolor: yellow"]3435[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Cancer[/TD]
[TD="bgcolor: transparent, align: right"]27.08[/TD]
[TD="bgcolor: transparent, align: right"]27.08[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-3523[/TD]
[TD="bgcolor: transparent"]VANN, WENDIE[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Cancer[/TD]
[TD="bgcolor: transparent, align: right"]24.9[/TD]
[TD="bgcolor: transparent, align: right"]24.9[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-2773[/TD]
[TD="bgcolor: transparent"]WASHINGTON, SYLVIA[/TD]
[TD="class: xl63, bgcolor: yellow"]3435[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]21.99[/TD]
[TD="bgcolor: transparent, align: right"]21.99[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-8740[/TD]
[TD="bgcolor: transparent"]WILLIAMS, ASHANTI[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-5681[/TD]
[TD="bgcolor: transparent"]WILSON, CHRISTINA[/TD]
[TD="class: xl63, bgcolor: yellow"]3226[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Cancer[/TD]
[TD="bgcolor: transparent, align: right"]34.31[/TD]
[TD="bgcolor: transparent, align: right"]34.31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"] [/TD]
[TD="bgcolor: transparent"] xxx-xx-7111[/TD]
[TD="bgcolor: transparent"]WRIGHT, TRINA[/TD]
[TD="class: xl63, bgcolor: yellow"]3435[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"] xxx-xx-1354[/TD]
[TD="bgcolor: transparent"]YOUNGBLOOD, ALEXUS[/TD]
[TD="class: xl63, bgcolor: yellow"]3160[/TD]
[TD="class: xl63, bgcolor: yellow"]COI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Accident[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[TD="bgcolor: transparent, align: right"]11.31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: yellow"] #N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: yellow"] #N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: yellow"] #N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: yellow"] #N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: yellow"] #N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: yellow"] #N/A[/TD]
[TD="class: xl63, bgcolor: yellow"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


My question is, similar to how was done in the post before, how do you delete any #N/A cells that do not have any corresponding info in the other columns along the same row, but keep the #N/A cells that does have corresponding info? Is there a work-around on that?

This will help me tons!

Thanks.
 
Upvote 0
Assumes data begins in Column A

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to delete row based on cell value?
Replies
5
Views
221
Peter_SSs
Peter_SSs
S
  • Solved
Delete rows that contain highlighting
Replies
5
Views
112
Serafin54
S
R
  • Question Question
Auto deleting a date column once the date has passed via VBA
Replies
4
Views
96
RMH2024
R
D
  • Question Question
VBA macro for combining and deleting duplicate rows, and adding values from one column
Replies
0
Views
90
Darko
D
B
  • Question Question
Find Range of Rows to Delete using FIND
Replies
8
Views
119
Skyybot
Skyybot

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top