PDA

View Full Version : Macro/vba to merge data



nnick
07-14-2015, 11:34 AM
Good day everyone!

I am having a bit of trouble figure out how to go about coding a macro to do what I want. I hope what I'm trying to do it not too hard.

What I'm trying to do is merge columns B through E into column B with a comma in between everything (I don't want to select which columns but rather provide static columns in the code, with the ability to change them when needed) Then I want to delete the data in columns in C through E.

In addition, I want a way for it to skip empty rows (needed for future use) and for the loop to end when 5 consecutive empty rows occur. (Again for future use, I'd like to have the ability to be able to change the value of 5 to something else.

I have attached an example of what I have and what my goal is. The data is just used as an example. The real document goes up to column J and has roughly 10,000 rows but I'm only interested in columns B through E with every row


13925

Thanks for everyone that can help!

snb
07-14-2015, 11:55 AM
Sub M_snb()
[A21:E22] = [index(choose(column(A6:E7),A6:A7,if(B6:B7="","",B6:B7&","&C6:C7&","&D6:D7&","&E6:E7),"","",""),)]
End Sub

nnick
07-15-2015, 04:45 AM
Thanks for responding but I'm quite confused on what your code does? From looking at it, it seems to be unique to the example I've uploaded, but that was an example and the real excel document contains about 10,000 rows or so. I am only interested in columns B through E and I'm not sure why "A" is there.

Can you please explain this to me? Maybe I'm missing something