delete error/blank cells

[Ask MS]

Happy Friday!

My question is: if a worksheet contains both numerical and error cells, how
can I delete all error cells (not just delete the error contents)
simultanuously?

 

[Gary Brown]

vba code:
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Delete

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Ask MS]

Hi, Gary

Thanks for your quick reply. This is my first time hearing of VBA. Do you
mind give me a brief introduction and how exactly I can get to the
"Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Delete" path in Excel?

Thanks a lot

 

[Gary Brown]

1) Open the workbook that you want to work on
2) Go to the worksheet that you want to work on
3) From the top menu, select Tools>Macros>Visual Basic Editor
4) In the left side, you will see a window with the title of 'Project -
VBAProject'
5) In that window, you will see your Excel file name
6) The sheet that you want to work on will probably be highlighted.
7) From the top menu, select Insert>Module
8) The window on the left should now show 'Module1'
9) A blank window should have appeared in the right window
10) in the new blank window on the right, create a procedure called
"DeleteStuff" by typing the following...

Public Sub DeleteStuff()
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Delete
End Sub

11) Get back into Excel by selecting from the top menu File>Close and
Return to Microsoft Excel
12) SAVE YOUR WORKBOOK!!!!!!!!!!! because we are about to run the macro
13) From the top menu, select Tools>Macro>Macros
14) Select the macro 'DeleteStuff
15) Select 'Run'

If there are no cells that need to be deleted, you will get an error, but
that's ok.

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown