0

I am trying to create a macro in which I want to delete and cut some values and then paste that values in next cell.
e.g.

 A                                    
----                                  
apple, fruit

After macro execution

 A          B                               
----       --- 
apple      fruit

How could I make such macro for an lengthy list?

Community
  • 1
  • 1
Nikhil G
  • 1,536
  • 3
  • 13
  • 28
  • possible duplicate of [Split cell string into individual cells](http://stackoverflow.com/questions/13020165/split-cell-string-into-individual-cells) – brettdj Apr 03 '15 at 08:40
  • @brettdj - From the comment attached to the line of comma delimited data in the sample, I take this as wanting to join the two, not split. IOW, the result is at the top, the bottom two-cell layout is what is started with. –  Apr 04 '15 at 03:13
  • If you are looking to join the two cells at the bottom of your sample data into a single cell separated by a comma then look at the many examples of native [CONCATENATE function](https://support.office.com/en-gb/article/concatenate-function-870e82a1-d47d-440e-9a77-23e7639eda1d) formulas and custom string concatenation on this site (e.g. [Concatenate 2 columns from a Sheet and paste them in another](http://stackoverflow.com/questions/19956515/excel-2010-vba-concatenate-2-columns-from-a-sheet-and-paste-them-in-another/27876681#27876681)) –  Apr 04 '15 at 03:20
  • @jeeped you are correct. Wrong duplicate, but will leave close vote in place given this has been covered before, and nothing appears to have been attempted as a self solution. – brettdj Apr 04 '15 at 04:02

2 Answers2

0

If you want to make some columns from one column in Excel follow this:

At first select all cells you want to split them.

In Data tab One of tools listed in Data Tools section is text to Columns.

After selecting that, a wizard window will show.

Select Delimited option, and Next.
Select Comma check, and Finish.

If you need a macro for it use Macro Recorder.

shA.t
  • 16,580
  • 5
  • 54
  • 111
0

You could try this:

Sub cutAndPaste()
'
' cutAndPaste Macro
'
'
Dim Column_index As Integer
Dim Row_index As Integer
Column_index = 5
Row_index = 7

Cells(Row_index + 1, Column_index).Value = Cells(Row_index, Column_index).Value
Cells(Row_index + 1, Column_index + 1).Value = Cells(Row_index, Column_index + 1).Value
Cells(Row_index, Column_index).Value = ""
Cells(Row_index, Column_index + 1).Value = ""

End Sub

I think it works, hope be helpful.

Omar Ramirez
  • 1
  • 1