Conditional Merges using VBA

[souvarine]

Hello, I am trying to merge a number of cells in a specific column, if a given condition is met--the cells must contain identical information.

Here is the code I started with:
Range("A2:A5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge

The problem is, I cannot hardcode the ranges as the number of identical cells may not be uniform (I may have 4 identical cells like in the example, but the next set could contain only 2). All identical cells will follow one another, so the dataset will have blocks of identical sets. Essentially, I am working with repeated measures, and I am trying to group/merge cells in the participant identifier column based on their unique identifier.

If anyone can offer any solutions, I would be very grateful

 

[Andrew Poulsom]

If I were you I would avoid merged cells, as they cause more problems than they solve and make any data analysis difficult. If you don't want to see the repeats use conditional formatting with a white font.

 

[souvarine]

I agree that merged cells do make life more problematic... it's just that I need the cells merged in order to clear up clutter on graphs I will output. By collapsing the cells together, that will stop Excel from repeating the same observation name multiple times on the X axis of the graph. I am not too concerned about analysis issues, as the bulk of the analysis (sans graphing) will be done through SAS.

I am unfamiliar with conditional formatting, but will this feature allow me to keep Excel from repeating observation names on a graph?

An aside:
I went ahead and created a variable on the spreadsheet that looks for matching cases using the formula: =IF(A3=A2,1,0) . Since this formula is in column F, I am trying to get this VBA code to work:

Dim lastMerge1 as Integer
Dim lastMerge2 as Integer
Dim lastMerge3 as Integer
Dim lastMerge4 as Integer

For i = 2 to 9
If cells(i, 1) = "patientA" and cells(i+1,6)=1 then lastMerge1=i+1
Else: If Cells(i,1)="patientB" and cells(i+1,6)=1 then lastMerge2=i+1
Else: If Cells(i,1)="patientC" and cells(i+1,6)=1 then lastMerge3=i+1
Else: If Cells(i,1)="patientD" and cells(i+1,6)=1 then lastMerge4=i+1
End If
Next i

and then use lastMerge1 ~ 4 as ending points for the merges:
Range("A2:A" & lastMerge1).Select ... etc.

Unfortunately, I am running into syntax errors. I am a novice to VBA coding and am having difficulty in untangling this mess. Again, any advice would be greatly appreciated!

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    Application.DisplayAlerts = False
    With ActiveSheet
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = LastRow To 2 Step -1
            If .Range("A" & i).Value = .Range("A" & i - 1).Value Then
                Application.Union(.Range("A" & i).MergeArea, .Range("A" & i - 1)).Merge
            End If
        Next i
    End With
    Application.DisplayAlerts = False
End Sub

 

[souvarine]

Thank you, that code worked!!