OK Almost There VBA After inserting merged range I need to get value from cell below

[cizzett]

So I am using this code to seperate my data so that each line with the sAME NAME IN COLUMN d ARE TOGETHER BUT A MERGED GROUP OF CELLS ARE INSERTED BEFORE EACH NEW NAME. aFTER THE CELLS ARE INSERTED THE ROW IS MODIFIED AND ALL WORKS PERFECT SO FAR.

What I'm still needing some help with is once I insert the cells and merge and format them I want to get the data from column D in the row below the new merged range and paste it in as Text in the new merged range.

Ideas?
Thanks in advance for any help or guidance.

Sub IRCV()

   Dim lRow As Long
   For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 3 Step -1
      If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then Cells(lRow, 1).Resize(, 10).Select: Selection.Insert xlDown
      Selection.MergeCells = True
      
    With Selection.Interior
        .ThemeColor = xlThemeColorLight1
    End With
    With Selection.Font
        .Name = "3M Circular TT Bold"
        .Size = 12
        .ThemeColor = xlThemeColorDark1
    End With
      
   Next lRow
   
End Sub

 

[Logit]

.
Does this work ?

Option Explicit


Sub IRCV()


   Dim lRow As Long
   For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 3 Step -1
      If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then Cells(lRow, 1).Resize(, 10).Select: Selection.Insert xlDown
      Selection.MergeCells = True
      Selection.Value = Cells(lRow - 1, "D").Value
      
      
    With Selection.Interior
        .ThemeColor = xlThemeColorLight1
    End With
    With Selection.Font
        .Name = "3M Circular TT Bold"
        .Size = 12
        .ThemeColor = xlThemeColorDark1
        .Color = RGB(255, 255, 255)
    End With
      
   Next lRow
   
End Sub

 

[Fluff]

Maybe

Sub IRCV()

   Dim lRow As Long
   For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 3 Step -1
      If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then
         With Cells(lRow, 1).Resize(, 10)
            .Insert xlDown
            With .Offset(-1)
               .MergeCells = True
               .Value = Cells(lRow + 1, "D")
               .Interior.ThemeColor = xlThemeColorLight1
               With .Font
                  .Name = "3M Circular TT Bold"
                  .Size = 12
                  .ThemeColor = xlThemeColorDark1
               End With
            End With
         End With
      End If
   Next lRow

End Sub

 

[cizzett]

Fluff you are always awesome, thanks to all for the guidance.

Fluff, yours is cleaner than mine and I hope to one day operate at your level.

This is working perfectly now.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Logit]

.


[h=2]You Received An Answer - Now What ?[/h]

Whether the answer you receive solves your issue or it doesn't ... it is rude to ignore
those who volunteered to assist you.

Let the volunteers know if their assistance was successful or not. DO NOT simply 'walk
away' and not respond.

Participation in a FORUM is the same as having a face to face conversation. How would you
feel if the person you are having a conversation with suddenly walked away without saying
anything ?

Dude ... don't be rude.​

 

[cizzett]

Fluff you are always awesome, thanks to all for the guidance.

Fluff, yours is cleaner than mine and I hope to one day operate at your level.

This is working perfectly now.

.


You Received An Answer - Now What ?

Whether the answer you receive solves your issue or it doesn't ... it is rude to ignore
those who volunteered to assist you.

Let the volunteers know if their assistance was successful or not. DO NOT simply 'walk
away' and not respond.

Participation in a FORUM is the same as having a face to face conversation. How would you
feel if the person you are having a conversation with suddenly walked away without saying
anything ?

Dude ... don't be rude.​

Logit, If you read my response in line one I say "Thanks to all for guidance".........

Not sure what else you'd have been looking for but,

Thanks Logit for offering guidance I appreciate you taking time to offer assistance and guidance in my search for the solution. You have helped me in the past and I appreciate you.

 

[Logit]

.
Thank you cizzett. Cheers !