need to remove rows containing the data #N/A Excel

Good morning Experts,

I've got a series of code that is working fine except it will not remove the rows where a cell  containing the data "#N/A" exists.

Is there a way through macros to fix this?

I've attached a working copy of Pahse 7 including the data sheets populated for your convenience.
Who is Participating?
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.

RedstoneITAuthor Commented:
! sec attaching file file
RedstoneITAuthor Commented:
here it is
Which button should remove the rows containing "N/A" in Which Sheet, Look to Which Column Values ?
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

RedstoneITAuthor Commented:
I'll have a separate sub routine called cleanUPStage5

Row "L" is the issue

RedstoneITAuthor Commented:
Found the solution. Had to save the excel spreadsheet as a CSV then was able to reopen with the #N/A gone.


On Error Resume Next
Columns("L:L").SpecialCells(2, 16).EntireRow.Delete
On Error GoTo 0

Needs some fine tuning, but this should work :

Sub CleanUPStage5()
' Stage 5 removes all rows from sheet "EQ Totals"
' If The value under Column "L" is "N/A"
Dim LastRow
Dim ColumnNumber

'MsgBox CStr(Cells(13, 12).Value)
ActiveWorkbook.Sheets("EQ Totals").Select
ColumnNumber = 12 'Corresponding to Column-L
'Determine the Last Non-Blank cell in that column
LastRow = ActiveSheet.Columns(ColumnNumber).SpecialCells(xlLastCell).Row
MsgBox "Last Row Number is = " & LastRow
ActiveSheet.Range(Cells(1, ColumnNumber), Cells(LastRow, ColumnNumber)).Select
Application.ScreenUpdating = False

On Error Resume Next

Dim i

For i = 1 To LastRow
If IsError(Range("L" & i).Value) Then
If Range("L" & i).Value = CVErr(xlErrNA) Then
Range("L" & i).EntireRow.Delete
i = i - 1
End If
End If
Next i

Application.ScreenUpdating = True
MsgBox "Cleanup Stage 5 Complete!"

End Sub

Open in new window


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
A different routine. As the removal of 30000+ rows is slow I have added intermediate views to show progress.
Hope it helps  

Or using krishnakrkc's suggestion - much more elegant and faster. Expert-Phase-7-cbb3.xlsm
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.