CODEWITHSUNDEEP
excelvba
0x90
0x90

Reputation: 40982

How to build a conditional cells merging in excel 2010?

I have a lot of raw data in 3 languages, when most of it is in .xlsx. You can think about the data as table contains 3 columns and a lot of rows(millions).

I want to build a macro which implement this:

checks if the cell on your adjacent cells is empty, merge yourself to the above cell.

if (the cell on your left is empty):
     merge yourself to the above cell;

I have no knowledge in VB, but I want to implement it by myself, how can I do it?

Upvotes: 0

Views: 3333

Answers (1)

Tony Dallimore
Tony Dallimore

Reputation: 12413

It is difficult to answer without more information but this outlines some of the issues.

You need somewhere to store the row and column of the cell of interest:

Dim ColCrnt As Long
Dim RowCrnt As Long

If you want to run down a column, you could write something like:

ColCrnt = 5     ' 5 represents column "E".  Columns are numbered from 1.

For RowCrnt = 1 to 1000
  'Update code.
Next

If you want to move the cursor around and then call the macro, you could write something like:

ColCrnt = ActiveCell.Column
RowCrnt = ActiveCell.Row
' Update Code.

Assuming the update code is to operate on the active worksheet, it will be something like:

If Cells(RowCrnt, ColCrnt - 1).Value = "" Then
  Cells(RowCrnt-1, ColCrnt).Value = _
          Cells(RowCrnt-1, ColCrnt).Value & Cells(RowCrnt-1, ColCrnt).Value    
  Cells(RowCrnt, ColCrnt).Value = ""
End If

Cells(RowCrnt, ColCrnt).Value is the value of the current cell. Subtracting 1 from RowCrnt, references the cell above. Subtracting 1 from ColCrnt, references the cell to the left.

Cells(RowCrnt-1, ColCrnt).Value = Cells(RowCrnt-1, ColCrnt).Value & Cells(RowCrnt-1, ColCrnt).Value concatenates the value of the current cell to the end of the cell above.

Cells(RowCrnt, ColCrnt).Value = "" clears the current cell.

So:

       |    E    |
       |---------|
       | The     |
       | cat     |

becomes:

       |    E    |
       |---------|
       | Thecat  |
       |         |

If you want a space between "The" and "cat":

  Cells(RowCrnt-1, ColCrnt).Value = _
       Cells(RowCrnt-1, ColCrnt).Value & " " & Cells(RowCrnt-1, ColCrnt).Value

If you want "cat" on a new line::

  Cells(RowCrnt-1, ColCrnt).Value = _
      Cells(RowCrnt-1, ColCrnt).Value & vblf & Cells(RowCrnt-1, ColCrnt).Value

Note: a cell can take a very long string but only the beginning will be visible.

Hope this gets you started.

Upvotes: 1

Related Questions