VBA Insert Shape next to each merged cell

[tonyjyoo]

Hello,

I want to utilize a macro to place left braces next to each merged cell. I already have a macro that goes down a column and merges groups of cells (going from A,B,C,etc):

Sub MergeCells()


  Dim LastRow As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  For Each Ar In Range("F15:F" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1).Resize(Ar.Count + 1).Merge
  Next
  
End Sub

I am trying to incorporate this line of coding to automatically place left braces around each merged cell, but it seems to only paste the shape in a static place in my file:

ActiveSheet.Shapes.AddShape(31, ActiveCell.Left, ActiveCell.Top, 6, 32.25).Select

How can I write my VBA so that after each merge, there follows a brace? For example, group "A" would be merged, then a brace would be pasted; group "B" would be merged, then a brace would be pasted, etc.

Thank you,
Tony

 

[tonyjyoo]

***Also, if there is a possibility, I would want the brace to autosize itself according to each merged group of row's height as well.

 

[Osvaldo Palmeiro]

Hi.
See if this could help.

Sub MergeCells()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  For Each Ar In Range("F15:F" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1).Resize(Ar.Count + 1).Merge
    ActiveSheet.Shapes.AddShape 31, Ar.Offset(-1, -1).Left + 30, _
     Ar.Offset(-1, -1).Top, 6, Ar(1).Offset(-1).Resize(Ar.Count + 1).Height
  Next
End Sub

 

[tonyjyoo]

Hi.
See if this could help.

Sub MergeCells()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  For Each Ar In Range("F15:F" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1).Resize(Ar.Count + 1).Merge
    ActiveSheet.Shapes.AddShape 31, Ar.Offset(-1, -1).Left + 30, _
     Ar.Offset(-1, -1).Top, 6, Ar(1).Offset(-1).Resize(Ar.Count + 1).Height
  Next
End Sub

This works beautifully. Just adjusted the parameters slightly to fit my file. Thank you so much!