Having error with 'Print #1, Join(Application.Transpose(Selection), vbCrLf)' line of code when a single cell is selected

[jfarc]

I have been using the following code (and variations of) for 6 years now with no issues until now. This is where I got the code in the first place - http://www.mrexcel.com/forum/excel-...lected-range-save-txt-file-2.html#post2235815

This code takes data from a pre-Selected Range of Cells (ex: A1:A120) in a single column and saves the data to a .txt file named in the 'Open' line of the code.

Sub f()
    Open "C:\TEXTFILE.txt" For Output As #1
    Print #1, Join(Application.Transpose(Selection), vbCrLf)
    Close #1
End Sub

In this past the Selected Range has always included multiple cells in the single column. This time there is only data in a single cell, so the pre-Seleted Range is 'A1:A1'. When the above code runs , it errors out on the Print #1.... line of code with err code .

I can easily test to see if there is only a single cell Selected and perform a different line of code to save this single cell to the .txt file, but I'm not sure what the new line of code should be to take the place of the 'Print #1....' line above. Or of there was a new line of code to replace the 'Print #1...' line above that would execute when there are multi-cells selected or just a single cell selected.

 

[Rick Rothstein]

The problem is you cannot Transpose a single cell. I think the following should work for you...

    Open "C:\TEXTFILE.txt" For Output As #1
    If Selection.Count = 1 Then
      Print #1, Selection
    Else
      Print #1, Join(Application.Transpose(Selection), vbCrLf)
    End If
    Close #1

 

[jfarc]

That was it. I installed your code suggestion and it worked perfectly. Thank you!