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.
RemoveLines.zip (232.2 KB)