如何在整列的单元格空格后删除任何内容？

I have a column of something that would be like XXX US, and I want to return XXX for the cell. I want to make a macro that deletes the whole column with one click. For some reason my ticker part of my code throws an error, but when i don't use a loop it works. Is there anything I can do?I have a column of something that would be like