Delete Data

I would like to delete all rows with the value #N/A in column H. How do I make this happen?

Thanks!

Chris
cansevinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh VCommented:
Hi,
Please use the below:

Sub Check_Blank_And_Delete_Entire_Rw()
Dim wb As Workbook
Dim ws As Worksheet
Dim lLastRow As Long
Dim strRowNums As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
lLastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    For i = 1 To lLastRow
        If IsError(Sheets(1).Range("H" & i).Value) Then
            strRowNums = strRowNums & i & ":" & i & ","
        End If
    Next i
    strRowNums = Left(strRowNums, Len(strRowNums) - 1)
    ws.Range(strRowNums).Select
    Selection.EntireRow.Delete
    ws.Cells(1, 1).Select
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ramesh VCommented:
Change the sheet name according to your requirement.
Roy CoxGroup Finance ManagerCommented:
Try this. It creates a Range to delete, so will be slightly faster than deleting individual rows

Option Explicit

Sub DeleteErrors()
    Dim rRng As Range, rCl As Range, rDelete As Range
    With ActiveSheet
        On Error GoTo exit_proc
        Set rRng = .Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp).SpecialCells(xlCellTypeFormulas))
        On Error GoTo 0
        On Error Resume Next
        For Each rCl In rRng
            If Application.WorksheetFunction.IsNA(rCl) Then

                If rDelete Is Nothing Then
                    Set rDelete = rCl
                Else: Set rDelete = Union(rDelete, rCl)
                End If
            End If
        Next rCl
        If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
    End With
    Exit Sub
exit_proc:
    MsgBox "No formulas found"
End Sub

Open in new window

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Ramesh VCommented:
Hi,
The 1st code also deletes all the rows at once, not one by one.
Ramesh VCommented:
Choose the best one :)
Saurabh Singh TeotiaCommented:
I'm assuming #N/A is the part of formula result which you want to delete then you can do this without running a loop or checking each row in faster manner by simply doing this..

Sub deleterng()

    Dim rng As Range, lrow As Long

    lrow = Cells(Cells.Rows.Count, "h").End(xlUp).Row

    Set rng = Range("H1:H" & lrow).SpecialCells(xlCellTypeFormulas, 16)

    If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub

Open in new window


However if its a value and not a formula..then use this code..This will give the same result only difference is whether the #N/A values which you are checking is value or answer of a formula...

Sub deleterng()

    Dim rng As Range, lrow As Long

    lrow = Cells(Cells.Rows.Count, "h").End(xlUp).Row

    Set rng = Range("H1:H" & lrow).SpecialCells(xlCellTypeConstants, 16)

    If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub

Open in new window


Saurabh...
Rob HensonFinance AnalystCommented:
If its a repeated effort then using VBA is probably best way but if it is infrequent, just use Auto Filter. Apply filter and select #NA in the drop down for column H. Select all visible rows as a block will only delete those that are visible, leaving the rest intact when the filter is disabled.

Thanks
Rob H
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.