I’m trying to record a macro that includes a step that separates first names and last names into two columns using the Text to Columns function. When I run the macro, I get a Run-time error ‘1004’: No data was selected to parse. Can someone look and see if you can help me on this?
Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 1)), TrailingMinusNumbers:=True
I’ve tried to look up answers to this and someone had mentioned to use relative references which didn’t change my result.
Rachael Paar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Issue can be resolved with error handling, in case you have a blank cell:
If InStr(Selection.Value," ") > 0 Then Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 1)), TrailingMinusNumbers:=True
I chose Instr() to check for the space, so it can separate, as opposed to Len()>0 since it was more specific to the request.
Edit1:
Screenshot of what I tested (note that A4 is just " " which does populate N1); as is, this macro will split a 3-part name or into 3 columns or 2 part name into 2 columns).
3