[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-16
9
Medium Priority
?
178 Views
Last Modified: 2012-08-13
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.
Regards,
Brian  
0
Comment
Question by:RedstoneIT
  • 4
  • 2
  • 2
  • +1
9 Comments
 

Author Comment

by:RedstoneIT
ID: 36975908
! sec attaching file file
0
 

Author Comment

by:RedstoneIT
ID: 36975910
here it is
Phase-7.xlsm
0
 
LVL 5

Expert Comment

by:softpro2k
ID: 36975973
Which button should remove the rows containing "N/A" in Which Sheet, Look to Which Column Values ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RedstoneIT
ID: 36975992
I'll have a separate sub routine called cleanUPStage5

Row "L" is the issue

Regards,
Brian
0
 

Author Comment

by:RedstoneIT
ID: 36976182
Found the solution. Had to save the excel spreadsheet as a CSV then was able to reopen with the #N/A gone.
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 36976198
Hi,

Try


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


Kris
0
 
LVL 5

Accepted Solution

by:
softpro2k earned 2000 total points
ID: 36976300
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

0
 
LVL 1

Expert Comment

by:cben
ID: 36983131
Hi
A different routine. As the removal of 30000+ rows is slow I have added intermediate views to show progress.
Hope it helps  

 Expert-Phase-7-cbb.xlsm
0
 
LVL 1

Expert Comment

by:cben
ID: 36983156
Or using krishnakrkc's suggestion - much more elegant and faster. Expert-Phase-7-cbb3.xlsm
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question