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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
148
Peter_SSs
Peter_SSs
S
  • Solved
Delete rows that contain highlighting
Replies
5
Views
86
Serafin54
S
S
  • Solved
VBA for deleting rows in a table based on value in a column
Replies
1
Views
416
dreid1011
dreid1011
C
  • Solved
VBA code for changes in cell and delete cell value
2
Replies
10
Views
279
cooleomter
C
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
273
Umma123
U

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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