VBA to merge rows, below and above an specific cell value within the same column

[krsk92]

Hi there

I am new with VBA and I am trying to develop an excel macro for this topic, but I haven´t been able to find something that would meet my needs as a starting point, hence I am looking for some help to develop an excel macro that will merge rows, below and above an specific cell value within the same column

For example, I have the following set of data in column A:

A1-->Position name
A2-->Company name
A3-->Requeriments
A4-->AAAAA
A5-->BBBBB
A6-->CCCCC
A7-->Skills
A8-->XXXX
A9-->YYYY

The macro should look for the values in column A, and merge starting with the cell that contains "Requirements" and would finish above the cell that contains "Skills", as shown below:
A1-->Position name
A2-->Company name
A3-->Requeriments
AAAAA
BBBBB
CCCCC
A4-->Skills
XXXX
YYYY

Then the macro would loop through the other columns and do the same. Note that the other columns can have more rows of data that would need to be merged under requirements and skills.

Please let me know if you have any doubts, any help would be greatly appreciated!!

Regards,

 

[Yongle]

Test on a COPY of your worksheet
- VBA finds Requirements (not Requeriments) which I assume was a typo in your OP
- code below assumes Requirements and Skills can be found in EVERY column and that Requirements is ALWAYS in row 3


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Director[/td][td]Consultant[/td][td]Marketeer[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Company A[/td][td]Company B[/td][td]Company C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Requirements[/td][td]Requirements[/td][td]Requirements[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]A[/td][td]A[/td][td]A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]B[/td][td]B[/td][td]B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]C[/td][td]C[/td][td]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Skills[/td][td]D[/td][td]D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]X[/td][td]E[/td][td]E[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Y[/td][td]F[/td][td]Skills[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td]G[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td]H[/td][td]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td]Skills[/td][td]Z[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td]Y[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Before[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Director[/td][td]Consultant[/td][td]Marketeer[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Company A[/td][td]Company B[/td][td]Company C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Requirements
A
B
C[/td][td]Requirements
A
B
C
D
E
F
G
H[/td][td]Requirements
A
B
C
D
E[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Skills
X
Y[/td][td]Skills
X
Y[/td][td]Skills
X
Y
Z[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: After[/td][/tr][/table]

Sub MergeCellValues()
    Dim lastCell As Range, Require As Range, Skills As Range, cel As Range
    Dim R As String, S As String, c As Long
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.VerticalAlignment = xlTop

    For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
        Set lastCell = Cells(Rows.Count, c).End(xlUp)
        Set Require = Columns(c).Find("Requirements", lookat:=xlWhole)
        Set Skills = Columns(c).Find("Skills", lookat:=xlWhole)
    
[COLOR=#006400]'string for requirements[/COLOR]
        For Each cel In Range(Require, Skills.Offset(-1))
            R = R & cel & Chr(10)
        Next
        Require = Left(R, Len(R) - 1)
[COLOR=#006400]'string for skills[/COLOR]
        For Each cel In Range(Skills, lastCell)
            S = S & cel & Chr(10)
        Next
        Require.Offset(1) = Left(S, Len(S) - 1)
        
        R = "": S = ""
    Next c

[COLOR=#006400]'clear rows 5 onwards[/COLOR]
    Cells(5, 1).Resize(Rows.Count - 4).EntireRow.Delete

End Sub

 

[krsk92]

Works great, thank you very much for your help!