i dont think you can do that within excel because of the length or the uncertainty, and by that i mean as a function...
you can simply say =A1 & "," & A2 & "," & A3
but thats lengthy, my solution would be a very small bit of code, that would run through the whole column and put it into one string, although, im not sure the length limits of a cell in excel, can i ask why are you doing this? maybe there is a different way to accomplish the same thing?
I actually need the numbers in that format so I can copy and paste them into another program I am using (Viewpoint) to run a query for costs on those specfic job numbers. The access database contains the job numbers I need, but does not contain costs. I have to copy and paste into viewpoint where all our accounting data is. If I go directly to viewpoint to run the query, again, it's a lengthy process where I manually have to type in each job number seperated by a comma. I hope this makes sense.
select the column you want, and.. i can't remember the specific process because im using excel 2007 now, and there is no more file/edit menus etc, but anyway, select the column you want,and you should be able to say to save it or export it as a csv file (comma seperated value) and you should get what you want.
or, if you want it simpler, select everything you want, copy and paste into word as text, and then find and replace, find a carriage return (look under the special text) and then replace it with a comma.
or there are a few other ways you can do this, you could even create a macro to do it for you and copy it to word, which wouldnt be that hard.. acctually i will look into that and see if i can come up with something for you.. but for now you see what you gotta do as far as i know
well after a little bit of work, i've got it. this is what you need to do..
in the excel document you are working with, create a new macro with a shortcut key or whatever you want it to be.
now dispite what it looks like, this is acctually very simple code, the second two parts are used so that after you run the code and the information is gathered, it is stuck in the clipboard, so all you need to do is go where you want to put the data and hit paste, and tada!
also there are two variables you may need to change, the AcRow, and AcCol state where to first start looking for row being the row to start at and col being the number of the column you first are at.
if you have any questions, feel free to ask.
after that, paste this code in for the new macro:
Code:
Dim AcRow, AcCol As Integer
Dim strTemp As String
AcRow = 1 'change to which row you want it to start looking on
AcCol = 1 'change to which column you want it to look in (A = 1)
While Not Cells(AcRow, AcCol).Value = ""
strTemp = strTemp & Cells(AcRow, AcCol).Value & "," 'add current cell if not empty
AcRow = AcRow + 1
Wend
strTemp = Left(strTemp, Len(strTemp) - 1) 'remove ending comma
ClipBoard_SetData (strTemp) 'function i got from the forums to send the data to the clipboard.
next you need to paste this above where the new code went
Code:
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Declare Function GetClipboardData Lib "User32" (ByVal wFormat As Long) As Long
Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
and then last but not least, paste this below where the first code went.
Code:
Function ClipBoard_SetData(MyString As String)
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long
' Allocate movable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo OutOfHere2
End If
' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Function
End If
' Clear the Clipboard.
X = EmptyClipboard()
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere2:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Function
i dont think you can do that within excel because of the length or the uncertainty, and by that i mean as a function...
you can simply say =A1 & "," & A2 & "," & A3
but thats lengthy, my solution would be a very small bit of code, that would run through the whole column and put it into one string, although, im not sure the length limits of a cell in excel, can i ask why are you doing this? maybe there is a different way to accomplish the same thing?
Thank you, it worked great. Sorry I didn't reply sooner, busy with work and home. But it did work. I used the idea of copying the column to Word, then replaced the carriage return wih a comma. Worked perfectly.
1) Sort your column data into numerical order (if that is how you want it to end up)
2) Copy the data and paste into Word (insert a few spare paragraph marks first and paste at the 2nd of these) and make sure the columns stay selected
3) Go to >Table>Convert>Table to text>Separate text with>Paragraph Marks>Ok [The data will still be selected, but not the additional P marks]
4) Go to >Edit>Replace>FindWhat>^p>Replace With>,>Replace All
5) The last value will have a comma at the end of it, which can be easily removed, then the line copied and pasted into Excel.
It's not really an Excel answer but it works; I use this sort of method of manipulating and editing text/data all the time.
I agree with the pasting and transposing, however i have a sequential file of 200 pieces of data in 102 items per part, I need to transpose each data point into seperate rows with 102 columns, aside from writing my code out 102 times as below ant suggestions??
sub bleh()
dim startRow as long
dim i as long
dim wb as workbook
dim ws1 as worksheet
dim ws2 as worksheet
set wb = thisworkbook
set ws1 = wb.Sheets("Windes_Sequential")
set ws2 = wb.Sheets("Windes_rows")
startRow = 31
for i = 1 to 102
ws1.range("A" & startRow & ":A" & startRow +101).copy
ws2.range("A" & i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
startRow = startRow + 102
next i
end sub
