macro help

[ed.ayers315]

Hello Forum,

I found this searching threads and it works too well!

I want this macro to only work in selected cells, not the whole document.

Sub ReplaceCRLF()
  
  ActiveCell.Replace Chr(13), " ", xlPart  'x1Part apply to within cells (default)
  ActiveCell.Replace Chr(10) & Chr(10) & "N/A", " ", xlPart 'xlWhole apply to entire cell content
  
End Sub

 

[jproffer]

Sub ReplaceCRLF()
Dim Rng As Range
    For Each Rng In Selection
        Rng.Replace Chr(13), " ", xlPart  'x1Part apply to within cells (default)
        Rng.Replace Chr(10) & Chr(10) & "N/A", " ", xlPart 'xlWhole apply to entire cell content
    Next Rng
End Sub

Try that out on a copy of your data.

 

[ed.ayers315]

Thanks for trying jproffer,

It still deletes all of those items in the worksheet.

 

[mikerickson]

For Each oneCell in Selection
    oneCell.Value = Replace(Replace(oneCell.Value, vbCr, " "), vbLF & vbLF & "N/A", " ")
Next

Is the "N/A" that you are looking for actual text or an #N/A error value?

 

[ed.ayers315]

Re: macro help (Solved)

Perfect, thank you.

And yes, looking for the text N/A and char(10)'s

Worked great!!