How to achieve custom merging in excel?

Question

I know just basic to excel. I have got huge excel sheet from client . I am trying to explain with sample data what i want. Here is the input data in one sheet

What I want is- I enter row numbers in new sheet (or in some text file or excel) like row numbers in any format say Row2:Row3:Row4 then it merge those three rows like below

1. Keep the content of A2 as it is.
2. Merge the content of B1,B2,B3 under B2
3. Move the content of A3 and A4 under Column C1. This step is to good to have if possible but not mandatory

Output Screenshot

I am not sure how to achieve this ? Is it achievable through macro or formula or something else ?

Answer 1

I think this is a good chance to give you the fishing pole and teach you two basic methods in VBA.

Combining two cells into one

If i have two cells with data:

You can always reference a cell's contents by this format:

ThisWorkbook.Sheets(worksheet_name).Range(desirded_range).Value 

where you can enter the worksheet name and range. This method can be used to read from a cell, as well as writing to a cell. So in my example above I would use:

Public Sub combine()
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = ThisWorkbook.Sheets("Sheet1").Range("A1").Value & ThisWorkbook.Sheets("Sheet1").Range("A2").Value
End Sub

Notice how I can reference the range A1 as reading from it as well as assigning data to it.

Merging

Using the same example for my data:

To merge we use this format:

ThisWorkbook.Sheets(worksheet_name).Range(desired_range).Merge

This is very similar to the combining example. Keep in mind that desired_range can be a range and doesn't have to be a single cell so to merge in my example I would just use:

Public Sub Merge()
    ThisWorkbook.Sheets("Sheet1").Range("A1:A2").Merge
End Sub