Deleting query

[Uma Chauhan]

I want to delete NA from my spreadsheet without getting it deleted from middle of the words like original using vba. Please help

 

[Joe4]

Welcome to the Board!

Are you wanting to just remove the value, or delete the whole row?

If wanting to just remove the value, you can use Find and Replace, clicking on the Options and making sure that the "Match entire cell contents" check box is selected.

If wanting to delete the whole row, take a look at using Filters.

 

[Uma Chauhan]

Hi Joe4,

Thanks for the response, really appreciate it. I am afraid what I am looking for is neither though. I have content in the table with some cells only having NA as content which I am trying to remove but on doing that all the NA occurring in that sheet regardless of being in middle of a word like "Signal" are getting deleted, leaving behind words like Sigl which is complicating my readability. I hop I have clarified the issue. I am using the following code:

Cells.Select
Selection.Replace What:="NA", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

 

[Fluff]

Change xlPart to xlWhole

 

[Joe4]

Yes, that "LookAt" part is exactly the argument that I was talking about (Match entire contents...).
That is just the VBA representation of that Find/Replace. Your code is just that functionality recorded with the Macro Recorder.
So, if you had turned on the Macro Recorder and recorded the step I explained in my first post, you would have gotten the correct version of the code.

Note for future reference, if you have questions about some VBA code you have, it is usually best to post the code in your question, so we can see the exact code you are using.

 

[Uma Chauhan]

Thank you so much Fluff and Joe4. This worked

Joe4, Noted Sir! Thank you

 

[Fluff]

Glad we could help & thanks for the feedback