How to copy non-contiguous columns to a text file

[Rick Rothstein]

I would like to copy columns just B and J to a tab-delimited text file.

Sorry I am so late providing a macro for you, but the idea behind this macro
only just occurred to me.

Here is a macro that will create a tab-delimited file from the values in two
or more columns that you select. Select full columns using the Control Key
(the order you select the columns in is the order they will appear in with
tab characters between them) and then run this macro...

Sub CopyNonContiguousSelectedColumnsIntoTheClipboardForPastingElsewhere()
Dim X As Long, LastRow As Long, UnusedColumn As Long, Index As Long
Dim Formula As String, FileName As String, SplitAddr() As String
On Error GoTo BadSelection
UnusedColumn = 1 + Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
SplitAddr = Split(Selection.Address(0, 0), ",")
ReDim Cols(0 To UBound(SplitAddr))
For X = 0 To UBound(SplitAddr)
Formula = Formula & "RC[-" & (UnusedColumn - _
Asc(SplitAddr(X)) + 64) & "]&CHAR(9)&"
Next
Cells(1, UnusedColumn).Resize(LastRow).FormulaR1C1 = _
"=" & Left(Formula, Len(Formula) - 9)
Open "c:\temp\JoinedColumns.txt" For Output As #1
Print #1, Join(WorksheetFunction.Transpose(Cells(1, _
UnusedColumn).Resize(LastRow)), vbNewLine)
Close #1
Exit Sub
BadSelection:
MsgBox "One of the columns you selected contains no data!", vbCritical
End Sub

Actually, you do not have to select "full columns" if you don't want to (I
just thought that would be "more normal)... just Control selecting a single
cell per column (they do not have to be on the same row) would be
sufficient, but remember, the order the cells are selected in determines the
column order for the output. If you select two cells from the same column,
that column's values will be repeated at the order position the repeat
occurs in.

Note that I took a "shortcut" by hard-coding the output filename and path
(make sure you change it before you run the macro)... you can, of course,
replace this with a dialog box selector for picking the file if you need to
output more than one set of tab-delimited columns.

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

Whoops! Sorry, I left out one important line of code at the end and the two
ScreenUpdating lines that hide the actions taking place on the worksheet.
Here is all the code again with the missing lines added....

Sub CopyNonContiguousSelectedColumnsIntoTheClipboardForPastingElsewhere()
Dim X As Long, LastRow As Long, UnusedColumn As Long, Index As Long
Dim Formula As String, FileName As String, SplitAddr() As String
On Error GoTo BadSelection
Application.ScreenUpdating = False
UnusedColumn = 1 + Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
SplitAddr = Split(Selection.Address(0, 0), ",")
ReDim Cols(0 To UBound(SplitAddr))
For X = 0 To UBound(SplitAddr)
Formula = Formula & "RC[-" & (UnusedColumn - _
Asc(SplitAddr(X)) + 64) & "]&CHAR(9)&"
Next
Cells(1, UnusedColumn).Resize(LastRow).FormulaR1C1 = _
"=" & Left(Formula, Len(Formula) - 9)
Open "c:\temp\JoinedColumns.txt" For Output As #1
Print #1, Join(WorksheetFunction.Transpose(Cells(1, _
UnusedColumn).Resize(LastRow)), vbNewLine)
Close #1
Columns(UnusedColumn).Clear
Application.ScreenUpdating = True
Exit Sub
BadSelection:
Application.ScreenUpdating = True
MsgBox "One of the columns you selected contains no data!", vbCritical
End Sub


Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein used his keyboard to write :

Sorry I am so late providing a macro for you, but the idea behind this macro
only just occurred to me.

Here is a macro that will create a tab-delimited file from the values in two
or more columns that you select. Select full columns using the Control Key
(the order you select the columns in is the order they will appear in with
tab characters between them) and then run this macro...

Sub CopyNonContiguousSelectedColumnsIntoTheClipboardForPastingElsewhere()
Dim X As Long, LastRow As Long, UnusedColumn As Long, Index As Long
Dim Formula As String, FileName As String, SplitAddr() As String
On Error GoTo BadSelection
UnusedColumn = 1 + Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
SplitAddr = Split(Selection.Address(0, 0), ",")

ReDim Cols(0 To UBound(SplitAddr))

What does this ReDim do?

For X = 0 To UBound(SplitAddr)
Formula = Formula & "RC[-" & (UnusedColumn - _
Asc(SplitAddr(X)) + 64) & "]&CHAR(9)&"
Next

Very interesting way to build this formula! I like it. Why +64?

Cells(1, UnusedColumn).Resize(LastRow).FormulaR1C1 = _
"=" & Left(Formula, Len(Formula) - 9)
Open "c:\temp\JoinedColumns.txt" For Output As #1
Print #1, Join(WorksheetFunction.Transpose(Cells(1, _
UnusedColumn).Resize(LastRow)), vbNewLine)
Close #1
Exit Sub
BadSelection:
MsgBox "One of the columns you selected contains no data!", vbCritical
End Sub

The only thing I'd like to comment on is that code of this nature,
while more efficient, requires documentation to save having to figure
out what it's doing. Not a bad thing, though, as I'm a big fan of well
documented code.<g>