why VBA fails to delete the row?

aaaaa34

Board Regular
Joined
Feb 25, 2014
Messages
62
hey guys,
I have a vba code to delete some rows along the match list
If it is written "#value!" in score column (7), code should delete that row.
But it gives error and not deleting.
Here is the code
Code:
Sub delApple()
    Dim r As Long, n As Long, U As Range
        n = Cells(Rows.Count, 7).End(xlUp).Row
    For r = 2 To n
        If InStr(1, Cells(r, 7), "#value!") Then
            If U Is Nothing Then
                Set U = Cells(r, 7)
            Else
                Set U = Union(Cells(r, 7), U)
            End If
        End If
    Next r
    If Not U Is Nothing Then U.EntireRow.Delete
    Set U = Nothing
End Sub
Can you check the attached file and help me to fix the code please? File:https://jumpshare.com/v/jrNnaY5Yz5mqlQrx0HBJ
Thank you.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi @aaaaa34

I am not master in VBA but my try is below.
If it helps then good

Code:
Sub REMOVE_ROW()
    Dim r As Long, n As Long, U As Range
        n = Cells(Rows.Count, 7).End(xlUp).Row
        For r = n To 2 Step -1
            If Cells(r, "G").Text = "#VALUE!" Then
                Rows(r).Delete
            End If
        Next r
End Sub

Regards
Dhruva
 
Upvote 0
My friends,
Thanks a lot for your quick responses.
But those error cells not result of formula. Its like text and cell format general.

Also Special-K99,
Which line did you mean to replace? I have zero knowledge with Vba, just I tried to modify it but I failed as I told. Sorry.
 
Upvote 0
This one

Code:
        If InStr(1, Cells(r, 7), "#value!") Then

though Fluff's reply looks a quicker solution
 
Upvote 0
If they are text try
Code:
Sub aaaaa34()
   Range("G:G").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub
 
Upvote 0
All your three responses helped to fix it.
Thank you all for your friendship and valuable help.
Thank you so much.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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