Delete rows

[rollingzep]

Hi,

I have a excel workbook to which i import data from a Bloomberg feed.

It has 3 columns. I import this data using VBA. I just want to delete those entire rows which has "N.A." in col A.
When I did this using Loop in For, Next it errored out.

 

[kevin9999]

If you don't have tens of thousands of rows to delete, the following approach should suffice. Try it on a copy of your data.

Option Explicit
Sub Zap_NA()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ change to actual sheet name as required
    
    With ws.Range("A1").CurrentRegion
        .AutoFilter 1, "N.A*"
        If ws.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If
        .AutoFilter
    End With

End Sub

 

[Michael M]

Maybe this way

Sub DeleteRows()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "N.A", "#N/A", xlWhole, , False, , False, False
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[igold]

And if you did have a very large dataset, something like this should work...

Sub DeleteRows()
    Dim i As Long, arr, arr2, x As Long, ct As Long
    arr = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim arr2(1 To UBound(arr, 1), 1 To UBound(arr, 2))
    ct = 1
    For i = 1 To UBound(arr)
        If Not arr(i, 1) = "N.A" Then
            For x = 1 To 3
                arr2(ct, x) = arr(i, x)
            Next
            ct = ct + 1
        End If
    Next
    Range("A2").Resize(UBound(arr2, 1), UBound(arr2, 2)) = arr2
End Sub

 

[kevin9999]

Another variation if there's many thousands of rows in your dataset.

Option Explicit
Sub Delete_Rows_en_masse()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   'Change to actual sheet name, or sheet code name
    Dim LRow As Long, LCol As Long, i As Long, a, b
    
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    a = Range(ws.Cells(2, 1), ws.Cells(LRow, 1))
    ReDim b(1 To UBound(a), 1 To 1)
    
    For i = 1 To UBound(a)
        If a(i, 1) = "N.A" Then b(i, 1) = 1
    Next i
        
    ws.Cells(2, LCol).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(LCol))
    If i > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
        order1:=xlAscending, Header:=xlNo
        ws.Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
    Application.ScreenUpdating = True
End Sub

 

[rollingzep]

And if you did have a very large dataset, something like this should work...

Sub DeleteRows()
    Dim i As Long, arr, arr2, x As Long, ct As Long
    arr = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim arr2(1 To UBound(arr, 1), 1 To UBound(arr, 2))
    ct = 1
    For i = 1 To UBound(arr)
        If Not arr(i, 1) = "N.A" Then
            For x = 1 To 3
                arr2(ct, x) = arr(i, x)
            Next
            ct = ct + 1
        End If
    Next
    Range("A2").Resize(UBound(arr2, 1), UBound(arr2, 2)) = arr2
End Sub

Where is the call to Delete Rows?

 

[igold]

It does not delete rows per say. It really just fails to copy the rows with "N.A" to the new array which is what gets written back to your worksheet.