Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
I had a string VBA code but I have lost it.
BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
Try this
Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
hmmmmm
I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
Here is my take on a macro to do what you want...
Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _ Delimiter) & Delimiter Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... hmmmmm I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
maybe I am not saving the formula right.
Still get confused with VBA I opened a brand to doc. Copied and pasted the row that I want to considate in to one cell in colA ok np next went to the dev. tab selected view code right clicked the sheet insert module pasted the VBA code ctrl S save, Saved as excel07 macro enab. Close the VBA window typed in b1 =bigconcat(highlighted the reange) Then tried =bigconcat(a1:a1000,";") hit enter and know I am geting #value in the cell sorry guys, "Rick Rothstein" wrote: Here is my take on a macro to do what you want... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _ Delimiter) & Delimiter Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... hmmmmm I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
A minor oversight on my part. Use this macro instead...
Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long, IR As Range For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 Set IR = Intersect(Data, Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data, Rows(X)))), Delimiter) & Delimiter End If Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... maybe I am not saving the formula right. Still get confused with VBA I opened a brand to doc. Copied and pasted the row that I want to considate in to one cell in colA ok np next went to the dev. tab selected view code right clicked the sheet insert module pasted the VBA code ctrl S save, Saved as excel07 macro enab. Close the VBA window typed in b1 =bigconcat(highlighted the reange) Then tried =bigconcat(a1:a1000,";") hit enter and know I am geting #value in the cell sorry guys, "Rick Rothstein" wrote: Here is my take on a macro to do what you want... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _ Delimiter) & Delimiter Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... hmmmmm I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
Two things... First, it's a UDF (User Defined Function), not a macro. Second, it seems I left out the default value for the Delimiter. Only the function declaration is changed, but I'm repeating the entire UDF to make it easier to copy/paste...
Function BigConcat(Data As Range, Optional Delimiter As String = ", ") As String Dim X As Long, IR As Range For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 Set IR = Intersect(Data, Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data, Rows(X)))), Delimiter) & Delimiter End If Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... A minor oversight on my part. Use this macro instead... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long, IR As Range For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 Set IR = Intersect(Data, Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data, Rows(X)))), Delimiter) & Delimiter End If Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... maybe I am not saving the formula right. Still get confused with VBA I opened a brand to doc. Copied and pasted the row that I want to considate in to one cell in colA ok np next went to the dev. tab selected view code right clicked the sheet insert module pasted the VBA code ctrl S save, Saved as excel07 macro enab. Close the VBA window typed in b1 =bigconcat(highlighted the reange) Then tried =bigconcat(a1:a1000,";") hit enter and know I am geting #value in the cell sorry guys, "Rick Rothstein" wrote: Here is my take on a macro to do what you want... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _ Delimiter) & Delimiter Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... hmmmmm I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
Okay, you may find this version of the function more flexible to use. The first version I posted *requires* a contiguous range of cells; this version will work with multiple, non-contiguous ranges; for example, something like this...
=BigConcat("; ",A1:A10,B2:H2,J3:M9) The only problem (if this is even a problem) is that the Delimiter is no longer optional and there is no default for it (and it has been moved to the first argument position in order to allow an unspecified number of ranges to be passed into the function as a comma delimit list). Okay, here is the function code... Function BigConcat(Delimiter As String, ParamArray Data()) As String Dim X As Long, Z As Long, IR As Range For Z = LBound(Data) To UBound(Data) For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1 Set IR = Intersect(Data(Z), Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data(Z), Rows(X)))), Delimiter) & Delimiter End If Next Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... A minor oversight on my part. Use this macro instead... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long, IR As Range For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 Set IR = Intersect(Data, Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data, Rows(X)))), Delimiter) & Delimiter End If Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... maybe I am not saving the formula right. Still get confused with VBA I opened a brand to doc. Copied and pasted the row that I want to considate in to one cell in colA ok np next went to the dev. tab selected view code right clicked the sheet insert module pasted the VBA code ctrl S save, Saved as excel07 macro enab. Close the VBA window typed in b1 =bigconcat(highlighted the reange) Then tried =bigconcat(a1:a1000,";") hit enter and know I am geting #value in the cell sorry guys, "Rick Rothstein" wrote: Here is my take on a macro to do what you want... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _ Delimiter) & Delimiter Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... hmmmmm I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string contra VBA code
Given the BigConcat function is basically a "Join" function with delimiter,
I don't know how useful this variation on the last code I posted will prove to be, but it allows you to inject text strings into the list. For example, you can do this (in addition to using non-contiguous ranges in the list)... =BigConcat("; ",A1:A10,"Some Text in the middle of the list",B2:H2,J3:M9) Anyway, modifying the code to allow for this was relatively easy to do, so I did it.<g Here is the code which adds this feature into the mix... Function BigConcat(Delimiter As String, ParamArray Data()) As String Dim X As Long, Z As Long, IR As Range For Z = LBound(Data) To UBound(Data) If TypeName(Data(Z)) = "Range" Then For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1 Set IR = Intersect(Data(Z), Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data(Z), Rows(X)))), Delimiter) & Delimiter End If Next Else BigConcat = BigConcat & Data(Z) & Delimiter End If Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Okay, you may find this version of the function more flexible to use. The first version I posted *requires* a contiguous range of cells; this version will work with multiple, non-contiguous ranges; for example, something like this... =BigConcat("; ",A1:A10,B2:H2,J3:M9) The only problem (if this is even a problem) is that the Delimiter is no longer optional and there is no default for it (and it has been moved to the first argument position in order to allow an unspecified number of ranges to be passed into the function as a comma delimit list). Okay, here is the function code... Function BigConcat(Delimiter As String, ParamArray Data()) As String Dim X As Long, Z As Long, IR As Range For Z = LBound(Data) To UBound(Data) For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1 Set IR = Intersect(Data(Z), Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data(Z), Rows(X)))), Delimiter) & Delimiter End If Next Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... A minor oversight on my part. Use this macro instead... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long, IR As Range For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 Set IR = Intersect(Data, Rows(X)) If IR.Count = 1 Then BigConcat = BigConcat & IR.Value & Delimiter Else BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect( _ Data, Rows(X)))), Delimiter) & Delimiter End If Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... maybe I am not saving the formula right. Still get confused with VBA I opened a brand to doc. Copied and pasted the row that I want to considate in to one cell in colA ok np next went to the dev. tab selected view code right clicked the sheet insert module pasted the VBA code ctrl S save, Saved as excel07 macro enab. Close the VBA window typed in b1 =bigconcat(highlighted the reange) Then tried =bigconcat(a1:a1000,";") hit enter and know I am geting #value in the cell sorry guys, "Rick Rothstein" wrote: Here is my take on a macro to do what you want... Function BigConcat(Data As Range, Optional Delimiter As String) As String Dim X As Long For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1 BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _ WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _ Delimiter) & Delimiter Next Do While InStr(BigConcat, Delimiter & Delimiter) BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter) Loop BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter)) If InStr(BigConcat, Delimiter) = 1 Then BigConcat = Mid(BigConcat, Len(Delimiter) + 1) End If End Function -- Rick (MVP - Excel) " wrote in message ... hmmmmm I got an error meesage that took me here Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in =BigConcat(H3:H979,"; ") this is formula I enter. does the space matter So basiclly I copied and then went to the dev. tab view code insert module paste the formula ctrl S (save) close out the code page and tried to plug that sucker in The brackets () went to bold and everything after typing the message "Billy Liddel" wrote: Try this Function BigConcat(data As Range, Optional Delimiter As String) As String Dim c, str As String, temp As String Dim l As Integer, addr As String, msg Dim r As Long, col As Integer Dim Alert As String If Len(Delimiter) = 0 Then Delimiter = ", " Else: Delimiter = Delimiter End If ' Check that data will display in cell and ext if not For Each c In data ' Results can be rubbish if a cell in data range is empry If Not IsEmpty(c) Then l = Len(c) + l r = c.Row col = c.Column If l 1024 Then addr = Cells(r, col - 1).Address BigConcat = str Alert = MsgBox("Value: " & temp & Chr(10) _ & "Cell " & Cells(r, col - 1).Address, , "Last cell Included in Display!") Exit Function End If If Len(str) = 0 Then str = c Else str = str & Delimiter & c End If End If Next c BigConcat = Trim(str) End Function you can enter it =bigconcat(A2:A10) and it will use a comma between each cell. or you can enter the delimiter of choice =BigConcat(A2:A10,";") You could save the code in your Personal File then it will always be available if you need it again. If you have to create a personal file record a simple macro, save the file as Personal. This is saved in Excel's Start up directory. You can see the function under custom using the Function lists. HTH Peter " wrote: I had a string VBA code but I have lost it. BAsicly I am trying to take several rows of data combine them into one cell with a ; and spce between each row once combines. Pelase advise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to remove rows containing a character string | Excel Discussion (Misc queries) | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
convert text string to a number code | Excel Discussion (Misc queries) | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions |