Search row item that includes spaces or lines/Remove lines and spaces in workbook or datatable

Currently I’m trying to remove newlines and spaces by automating replace function (ctrl+H) of Excel,
to make it possible to implement DataTable.Rows.Item(columnName) or DataTable.Select(columnName = itemName ).

However this is not an elegant solution since the outcome of the replacement isn’t always the same (sometimes it works without popup window but sometimes it comes with a success/failure report window), It does not work with different Excel versions, and Send hotkey sometimes fails (ex. ctrl+j turns j) .

So I have multiple quiestions:

  • How do I search a row item that contains space or newline?

  • If not, how should I remove space and newline from a datatable or a workbook all at once?

  • If I were to implement this by sending hotkey (Ctrl+H→Ctrl+J) how can I standardize the reaction of different versions of Excel?

↓This used to work on my laptop (Win 10) with Microsoft Office 365 but now it doesn’t. (232.2 KB)