Efficient VBA code for conditionally deleting rows in big excel sheets

[akash27]

My excel sheet has 40 columns and more than 1,00,000 rows. I want to delete all the rows which contain cell with a string "NA" in any of the columns. I am struggling to find an efficient VB code for this on, which doesn't cause Excel to crash. Any suggestions?
P.S. Exact number of rows and columns are not known apriori. And, I'm new to VBA, any help with the complete code would be greatly appreciated.
Link to the concerned file - https://drive.google.com/file/d/0Bzl56SYDK-iRdTE1LTlYZGsxSE0/view?usp=sharing

My VBA code (this takes forever!):

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub DeleteRowWithContents()

'Finds the last non-blank cell on a sheet/range.

Dim lRow AsLong
Dim lCol AsLong

lRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

lCol = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column

'MsgBox "Last Row: " & lRow
'MsgBox "Last Column: " & lCol


For j = lCol To1Step-1
For i = lRow To1Step-1
If(Cells(i, j).Value)="NA"Then
Cells(i,"A").EntireRow.Delete
EndIf
Next i
lRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Next j


EndSub</code>

 

[mole999]

what does work is to filter the items you don't want out, copy the stuff you want to keep to a temp sheet, delete the redundant sheet and then rename the temp sheet. I had a routine that would take 5 hours, and reduced it to less than 10 seconds

 

[akash27]

Sounds clever!
Could you please elaborate and guide me to relevant VB functions that would be useful for this task.

 

[mole999]

you'll need to adjust for your needs

Private Sub Remove_Duplicates()

    Application.ScreenUpdating = False

    Dim lr As Long
    lr = ActiveSheet.Range("B150000").End(xlUp).Row

    'Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add.Name = "Process"

    Sheets("Sheet1").Select

    ActiveSheet.Range("$A$1:$AL$" & lr).AutoFilter Field:=37, Criteria1:=1

    Range("A1:Al" & lr).Select

    Dim rng As Range
    Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A1:Al" & lr))
    rng.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("process").Range("A1")

    Application.DisplayAlerts = False
    ' Application.EnableEvents = False
    Sheets("sheet1").Delete
    ActiveSheet.Name = "Sheet1"
    ' Application.EnableEvents = True
    Application.DisplayAlerts = True
    Columns("Aj:Al").Select
    Selection.Delete Shift:=xlToLeft

    Application.ScreenUpdating = True

End Sub

 

[Norie]

Using Find & Replace... replace all the NA with #N/A, then goto Find & Select>Goto Special>Constants>Errors to select all the #N/A and then right click Delete>Entire Rows.

 

[akash27]

No offense, but this is worse than looping as Excel crashes every time when the data is big.

Using Find & Replace... replace all the NA with #N/A, then goto Find & Select>Goto Special>Constants>Errors to select all the #N/A and then right click Delete>Entire Rows.

 

[Norie]

No offense, but what do you expect when dealing with a file of this size in Excel?