Delete Rows Macro on mutiple sheets

[BradH]

I have a workbook with about 120 sheets. I need a macro/vba that will search through half of these sheets and delete the entire row if it contains #N/A. The other half of these sheets also contain the #N/A, but I don't want it deleted in them.

Thanks

 

[BrianJN1]

I have a workbook with about 120 sheets. I need a macro/vba that will search through half of these sheets and delete the entire row if it contains #N/A. The other half of these sheets also contain the #N/A, but I don't want it deleted in them.

Thanks

Sorry Brad, but how do we help you?
See my signature.

 

[My Aswer Is This]

And how are we to know what 60 sheets?

Is it sheets 1 to 60?

And in what column on the sheet should we look for #N/A

If I said "go to store and get me something". Would you know what I wanted?

 

[vbanoob0807]

Hi BradH,

assuming half of your worksheets is from the 1st worksheet to num 60, and as long as any column, be it 1 or more, contains "#N/A", the entire row will be deleted, you can try the code below. Be sure to test on some test data because chances are delete is irreversible in VBA.

Sub test()

Dim c As Range
Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 60

    With ThisWorkbook.Sheets(i)

        Do
        
            On Error Resume Next
            c.EntireRow.Delete
            Set c = .Cells.Find(What:="#N/A", _
                                After:=.Cells(Rows.Count, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                MatchCase:=False)
            
        Loop While Not c Is Nothing
    
    End With
    
Next i

Application.ScreenUpdating = True

End Sub

 

[BradH]

Sub test()

Dim c As Range
Dim i As Integer


Application.ScreenUpdating = False


For i = 66 To 130


    With ThisWorkbook.Sheets(i)


        Do
        
            On Error Resume Next
            c.EntireRow.Delete
            Set c = .Cells.Find(What:="#N/A", _
                                After:=.Cells(Rows.Count, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                MatchCase:=False)
            
        Loop While Not c Is Nothing
    
    End With
    
Next i


Application.ScreenUpdating = True


End Sub

I went with this, and it works, but after 5 minutes I stopped it (To make sure it was actually working) and it had only completed 10 of the 65 sheets.

The #N/A is in every column, A-E on each sheet. If its in one column, its in all 5. Its an index lookup formula which is returning an error because there are no matches.

Any way I can improve this to run faster?

 

[Fluff]

edit

 

[Fluff]

Try this

Sub test()

    Dim i As Long
    
    Application.ScreenUpdating = False
    
    For i = 66 To 130
        With ThisWorkbook.Sheets(i)
            On Error Resume Next
            Columns(10).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
            On Error GoTo 0
        End With
    Next i

End Sub

One word of warning, this will delete any rows containing an error, not just #N/A

 

[vbanoob0807]

Hi BradH, does your data have headers? You can try this

Sub test()

Dim RngToFilter As Range
Dim c As Range
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

i = 66
For i = 66 To 130

    Set RngToFilter = ThisWorkbook.Worksheets(i).UsedRange
    RngToFilter.AutoFilter 1, "#N/A"
    RngToFilter.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    
    Set c = ThisWorkbook.Worksheets(i).Cells.Find(What:="#N/A", _
                                                    After:=Cells(Rows.Count, 1), _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlPart, _
                                                    MatchCase:=False)
                                
    If Not c Is Nothing Then
    
        c.EntireRow.Delete
    
    End If

Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[BradH]

Try this

Sub test()

    Dim i As Long
    
    Application.ScreenUpdating = False
    
    For i = 66 To 130
        With ThisWorkbook.Sheets(i)
            On Error Resume Next
            Columns(10).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
            On Error GoTo 0
        End With
    Next i

End Sub

One word of warning, this will delete any rows containing an error, not just #N/A

This did nothing.

 

[BradH]

Hi BradH, does your data have headers? You can try this

Sub test()

Dim RngToFilter As Range
Dim c As Range
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

i = 66
For i = 66 To 130

    Set RngToFilter = ThisWorkbook.Worksheets(i).UsedRange
    RngToFilter.AutoFilter 1, "#N/A"
    RngToFilter.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    
    Set c = ThisWorkbook.Worksheets(i).Cells.Find(What:="#N/A", _
                                                    After:=Cells(Rows.Count, 1), _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlPart, _
                                                    MatchCase:=False)
                                
    If Not c Is Nothing Then
    
        c.EntireRow.Delete
    
    End If

Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

This worked, but it only turned the filter off on the first sheet....causing all but the top row (which is not a header, but will never be a deleted row) to be hidden