Delete a row from another sheet where text equals #N/A

Question

I have built series of VBA macros to work through some steps for a user. I have built a button to for a user to click to remove any row that contains #N/A. One of my earlier steps makes this just a plain text value.

When executing the action it appears to run through but just moves my view to the worksheet i am trying to clean up and doesn't actually delete any rows.

I have tried switching parts of the code so instead of = to like

Sub DeleteNAsOnFrank_Click()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

With Sheets("Frank Import Full List")
    .Select
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    .DisplayPageBreaks = False
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    For Lrow = Lastrow To Firstrow Step -1
        With .Cells(Lrow, "A")
            If Not IsError(.Value) Then
                If .Value = "#N/A" Then .EntireRow.Delete
            End If
        End With
    Next Lrow
End With

ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With
End Sub

I expect it to delete any row that contains the text If .Value = "#N/A" Then .EntireRow.Delete on the Sheets("Frank Import Full List")

You know instead of looping and deleting, you could use Autofilter to delete all those rows in 1 go? `Sheets("Frank Import Full List").Range("A1:A" & Lastrow).AutoFilter Field:=1, Criteria1:="#N/A"` — Siddharth Rout, Aug 29 '19 at 11:57
And use [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) to find the last row. — Siddharth Rout, Aug 29 '19 at 11:58
You're not deleting because your `If Not IsError(.Value)` is tripping because `#N/A` **is** an error. Remove that test and it should work. — PeterT, Aug 29 '19 at 11:59

Siddharth Rout · Answer 1 · 2019-08-29T12:11:59.800

3

Change your code to

    With .Cells(Lrow, "A")
        If IsError(.Value) Then .EntireRow.Delete
    End With

However, I would recommend a better way to delete those cells. Use Autofilter instead of looping and deleting. Your code will be much more faster. Also instead of usedrange. Work with the actual range. Use This to find the last row and work with that range

Dim ws As Worksheet
Dim delRange As Range
Dim lRow As Long

Set ws = ThisWorkbook.Sheets("Frank Import Full List")

With ws
    .AutoFilterMode = False
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row

    With .Range("A1:A" & lRow)
        .AutoFilter Field:=1, Criteria1:="#N/A"
        Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    End With

    If Not delRange Is Nothing Then delRange.Delete

    .AutoFilterMode = False
End With

In Action

edited Aug 29 '19 at 12:11

answered Aug 29 '19 at 12:04

Siddharth Rout

147,039
17
206
250

How quick is using autofilter (your method) vs passing the range to an array and looping through that? – Tim Stack Aug 29 '19 at 12:11
Best is to try and find it out yourself :) BTW Using array in this case is not advised as you will have to take care of 2 operations. clear array row and after transposing, take care of blanks. you could take care of the rows in the array itself but then still it is "2 operations". – Siddharth Rout Aug 29 '19 at 12:14
True. I'll have to test and see for myself – Tim Stack Aug 29 '19 at 12:14
Yup. BTW whether it is array or autofilter, they both are definitely better than deleting rows in a loop :D. There is one more way to delete rows using loop. It is called the Union mehod where you delete once the loop gets over. – Siddharth Rout Aug 29 '19 at 12:17
Oh I agree all these methods listed are better than what I proposed... but it works ;) – Tim Stack Aug 29 '19 at 12:33

score 1 · Accepted Answer · answered Aug 29 '19 at 11:54

1

Try the following

'Code...
For Lrow = Lastrow To Firstrow Step -1
    If Application.WorksheetFunction.IsNA(.Cells(Lrow, "A")) Then .Rows(Lrow).EntireRow.Delete
Next Lrow
'Code...

answered Aug 29 '19 at 11:54

Tim Stack

3,209
3
18
39

Delete a row from another sheet where text equals #N/A

2 Answers2