RedstoneIT
asked on
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.
Regards,
Brian
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
ASKER
here it is
Phase-7.xlsm
Phase-7.xlsm
Which button should remove the rows containing "N/A" in Which Sheet, Look to Which Column Values ?
ASKER
I'll have a separate sub routine called cleanUPStage5
Row "L" is the issue
Regards,
Brian
Row "L" is the issue
Regards,
Brian
ASKER
Found the solution. Had to save the excel spreadsheet as a CSV then was able to reopen with the #N/A gone.
Hi,
Try
On Error Resume Next
Columns("L:L").SpecialCell
On Error GoTo 0
Kris
Try
On Error Resume Next
Columns("L:L").SpecialCell
On Error GoTo 0
Kris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Or using krishnakrkc's suggestion - much more elegant and faster. Expert-Phase-7-cbb3.xlsm
ASKER