Copy more than 255 characters in excel

The company has a huge assortment of excel reports, and despite not being my work, nor being expert on it, i give them a hand :)

One of the reports is a daily matrix of 100K+ cells, split between 8 analyst, where they get a list of links and other miscellaneous data from created entities. Each analyst in their report transformed all columns into one single one (think hundreds, copied one by one), order by name, retrieve all http... and throw in the tool to link check.

Besides macros to remove duplicates and empty cells, i wrote one to convert a matrix of cell into a single column. It works by selecting the pretended cells, run macro, point destination and its done.

Here it is:

Sub SingleColumnSelection()

Dim v       As Variant
Dim nCol    As Long
Dim nRow    As Long
Dim rOut    As Range
Dim iC开发者_运维技巧ol    As Long

v = Selection

nRow = UBound(v, 1)
nCol = UBound(v, 2)
Set rOut = Application.InputBox("Select destination", Type:=8).Resize(nRow, 1)
If rOut Is Nothing Then Exit Sub
For iCol = 1 To nCol
    rOut.Value = WorksheetFunction.Index(v, 0, iCol)
    Set rOut = rOut.Offset(nRow)
Next iCol
End Sub

I did it 2 months ago, now today they told me sometimes they have problems, i went digging and found 2 links that broke the macro (in a 20k report).

Both have around 300 characters, and the app breaks after 255 in rOut.Value = WorksheetFunction.Index(v, 0, iCol), when it is reading from the >255 character cells(i think).

Any workaround, to a excel illiterate, i have ideas and i am trying alternatives, but would change the work-flow for analyst in this report

Try this instead:

Sub SingleColumnSelection()

Dim rngOut    As Range
Dim rngSelection As Range
Dim rngCol As Range

Set rngSelection = Selection
Set rngOut = Application.InputBox("Select destination", Type:=8).Resize(rngSelection.Rows.Count, 1)
If rngOut Is Nothing Then Exit Sub

For Each rngCol In rngSelection.Columns
  rngCol.Copy Destination:=rngOut
  Set rngOut = rngOut.Offset(rngSelection.Rows.Count)
Next rngCol

End Sub