CODEWITHSUNDEEP
vbaexcel
MJ95
MJ95

Reputation: 479

VBA: Combining two sets of columns dynamically

I have a set of columns, with some columns in between, and then another set of columns, i.e.:

Before:

ColA ColB ColC ColA ColB ColC RandomCol1 RandomCol2 ColA ColB ColC ColA ColB ColC
 1    2     3    4    5    6    N/A         N/A       7    8    9   10   11   12

After:

ColA        ColB        ColC      RandomCol1   RandomCol2
 1;4;7;10   2;5;8;11    3;6;9;12     N/A         N/A

If first group is "blank":

Before:

 ColA    ColB    ColC   ColA    ColB   ColC   RandomCol1   RandomCol2  ColA   ColB  ColC  ColA   ColB   ColC
blank    blank   blank  blank   blank  blank    N/A         N/A           7     8    9    10      11     12

After:

ColA        ColB        ColC      RandomCol1   RandomCol2
7;10        8;11        9;12        N/A           N/A

I would like to combine the values in each row of each column with the same name separated by ; while then deleting the leftover columns. Furthermore, if the values in the first group are "blank" then it should only take the values from the second group (after random columns)

The random columns should not be combined

I have tried this which does not seem to work when there are random columns in between (also not sure how to add logic that skips first "group" if the value is "blank":

    For DestCol = StartCol To EndCol
   For ReadCol = DestCol + 1 To EndCol
      If Cells(1, DestCol) = Cells(1, ReadCol) Then
         For i = 2 To lastRow
            If Cells(i, ReadCol) <> "" Then
               Cells(i, DestCol) = Cells(i, DestCol) & ";" & Cells(i, ReadCol)
            End If
         Next i
      End If
   Next ReadCol
Next DestCol

Upvotes: 0

Views: 94

Answers (1)

user6432984
user6432984

Reputation:

You need to delete the duplicated column after they are processed.

Note: You'll notice that I shortened the name of the counters. I always use 1 letter names for the first counter and a letter with a number for similar counters. For example: If I use Cells(x, y) for the outer loop, I will use Cells(x1, y1) for the next inner loop. The reason that I do this is that counters are usually repeated several times in the code and long descriptive counter names cause clutter. This actually makes the code harder to read. 

Sub CombineColumns()
    Const STARTCOLUMN As Long = 1
    Const ENDCOLUMN As Long = 14

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim lastRow As Long, i As Long, y As Long, y1 As Long

    lastRow = Range(Columns(STARTCOLUMN), Columns(ENDCOLUMN)).Find(What:="*", After:=Cells(1, STARTCOLUMN), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

    For y = STARTCOLUMN To ENDCOLUMN
        For y1 = y + 1 To ENDCOLUMN
            If Cells(1, y) <> "" And Cells(1, y) = Cells(1, y1) Then
                For i = 2 To lastRow
                    If Cells(i, y1) <> "" Then
                       Cells(i, y) = IIf(Cells(i, y) <> "", Cells(i, y) & ";", "") & Cells(i, y1)
                    End If
                Next i
                Columns(y1).Delete
                y1 = y1 - 1
            End If
        Next y1
    Next y

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions