LinkBack URL
About LinkBacks
What is the vba formula to concatenate columns separated with "," only the cells where there is text inside. Do this untill the last row of text you can find.
Can you make this starting for column 2 up to the last column with text?
Thx
What is the vba formula to concatenate columns separated with "," only the cells where there is text inside. Do this untill the last row of text you can find.
Can you make this starting for column 2 up to the last column with text?
Thx
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
excel.xlsb
I hope this worked.
This is simplified what I want. I have a lot more data in different row format. So please do select untill last row
The after worksheet as you see is at row 10 (row above the content) concatenate all with "," , no blank rows.
Thx
Hi Broea,Originally Posted by BROEA
. It is not clear wot exactly you want. Try to give a screenshot which we can copy to a spreadsheet. That makes it easier for us to understand, and helps for anyone following the Thread. ( Notes for doing that in my signature ). Alternatively upload ( attach ) a workbook as clearly explained by Sixthsense – take particular note about the BEFORE and AFTER - that always describes exactly what Yous want the best. The AFTER should look exactly as Yous want after the macro is run based on the sample data Yous give in the BEFORE.
. Is this at least getting close to wot yous wanting?:
. Say you have this:
Using Excel 2007
- Tom1 Dick1 Arries1 Tom2 Dick2 Arries2 Tom3 Dick3 Arries3 Tom4 Dick4 Arries4 Tom5 Dick5 Arries5 Tom6 Arries6 Tom7 Dick7 Arries7
BROEA
……
Would this be maybe what you want it to look like after running any macro
Using Excel 2007
- Tom1 Dick1 Arries1 Tom1 , Dick1 , Arries1 Tom2 Dick2 Arries2 Tom2 , Dick2 , Arries2 Tom3 Dick3 Arries3 Tom3 , Dick3 , Arries3 Tom4 Dick4 Arries4 Tom4 , Dick4 , Arries4 Tom5 Dick5 Arries5 Tom5 , Dick5 , Arries5 Tom6 Arries6 Tom6 Arries6 Tom7 Dick7 Arries7 Tom7 , Dick7 , Arries7
BROEA
… if so, here is the code:
Please Login or Register to view this content.
Alan
'_- Google first, like this _ site:ExcelForum.com Gamut
Use Code Tags: Highlight code; click on the # icon above,
Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
http://www.excelforum.com/the-water-...ml#post4109080
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
.... Ahh... I sent my last post, before i saw your reply with an attachment...
.. I will take a look at that now...
Alan
Duplicate post
hi, thx for the post. This is exactly what I mean, but for columns instead of rows.
maybe so
Please Login or Register to view this content.
I adjust the macro to look untill the last column
Sub ttt()
Dim i&
Dim lastcol As Long
lastcol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
For i = 2 To lastcol
With Range(Cells(132, i), Cells(Rows.Count, i).End(xlUp))
Cells(131, i).Value = Replace(Join(Filter(Split("~" & Join(Application.Transpose(.Value), _
"~,~") & "~", ","), "~~", False), ","), "~", "")
End With
Next
End Sub
This does the trick what I want, but he gives an error on your command, I think because my specification of lastcolumn is not correct.
Can you add to the this when a column is blank inbetween to add a " " so the text doesn't go through the next cell. Also in the cell next to the lastcell (in this case E10)
thx
please use code tags around your code
and try it
Please Login or Register to view this content.
……….. And ‘ere comes the part-time amateur with his attempt….
BEFORE:
Using Excel 2007
-
BROEA
AFTER:
Using Excel 2007
- 1,2,2,4,5 2,3,4, 1,5
BROEA
…….
…..
Code:
Please Login or Register to view this content.
… maybe this is bit quicker for large files…
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks