Copy and Paste cells limitation

[Villy]

I need some help with Excel. I have a worksheet with over 1,000 rows. I
want to copy one column, which contains a value format like “1234-567â€, and
transpose the values to a row. I can’t use the traditional ‘copy-and-paste
special – transpose’ because there are too many cells across. Once I have a
row, I need to make it into one long string where each value is separated by
commas. Does anyone know how to do this?

I tried to transpose my column in chunks, into rows, and then copying into
Word, but Word brings over the borders so it displays as a table – which I
don’t want. And I don’t know how to add the comma separators.


Thanks for any help you can provide.

 

[Jacob Skaria]

Hi Villy

Try the below macro which will create a .csv file from the column
data..Adjust the range A1:A100 to suit your requirement. If you are new to
macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro()
Dim intFile As Integer
Dim rngTemp As Range
Set rngTemp = Range("A1:A100")

intFile = FreeFile
Open "c:\temp.csv" For Output As #intFile
Print #intFile, Join(WorksheetFunction.Transpose(rngTemp), ",")
Close #intFile

End Sub


If this post helps click Yes

 

[Pete_UK]

Leave your data in that column (assume column A, starting with A1).
Put this formula in B1:

=TEXT(A1,"0000-000")

Then in B2 you can put this formula:

=B1&", "&TEXT(A2,"0000-000")

and then copy this down to the bottom of the data in column A. A quick
way of doing this is to select B2, and then double-click on the fill
handle (the small black square in the bottom right hand corner of the
cursor). The combined data that you want will be in the bottom cell of
column B. Select that cell, click <copy>, then right-click and choose
Paste Special | Values (check) | OK then <Esc>. Then you could move
that cell to another more-convenient location at the top of your
sheet.

You can get rid of all the other formulae in column B above that cell.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I assumed you wanted a comma and space between the values. If
you only want a comma, then the formula in B2 should be:

=B1&","&TEXT(A2,"0000-000")

Hope this helps.

Pete

 

[Gord Dibben]

Copy this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =concatrange(A1:A1000)

Then copy/paste special>Values


Gord Dibben MS Excel MVP