Copy/paste Row 1 after every merged cell

omkar94

New Member
Joined
Apr 26, 2017
Messages
24
Hey Guys!

Im looking for a VBA code that will copy row 1 from column A to M , and will paste row 1, a line below every time it finds a merged cell. And I want this to loop until the last Merged cell. Please refer to my code below and see if it makes any sense to you.

Can someone please help. Im sure this is a very simple code, but Im just a beginner and cant figure it out.

My Code:

Sub Insertrow1 ()

Rows("1:1").Select
Selection.Copy
' Find next merged cell (Need help on this line)
ActiveCell.Offset(1, 0).Select
Selection.Insert Shift:=xlDown

' Loop (Need Help on this line)

End Sub


Thank you,

Omkar V
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,650
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Merging cells is never a good idea, especially when you are using VBA on a sheet that contains merged cells. That said, see if this does what you want...

Code:
Sub findMerged()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim c As Range, r1 As Range, rng As Range
    Dim sMsg As String
    Dim i As Long, rw As Long
    
    Set r1 = Rows("1:1")
    Set rng = ws.UsedRange
    rw = rng.Rows.Count
    For i = rw To 2 Step -1
        For Each c In rng.Rows(i).Cells
            If c.MergeCells Then
                r1.Copy
                ws.Range("a" & i).EntireRow.Offset(1, 0).Insert
                Exit For
            End If
        Next c
    Next
    Application.CutCopyMode = False
    
End Sub

I hope this helps.
 

omkar94

New Member
Joined
Apr 26, 2017
Messages
24
Oh man, you're like VBA god me right now!
Thanks a bunch, it works!!

Btw, the merged cells are there because, It is a report I export daily from a website, and I want to create a Pivot table after every merged cell. So I am going to try to create a code for that as well.
I'm also trying to learn VBA, any tips on how to be proficient at it?

Regards,

Omkar V
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,650
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks for the feedback. Like Vocabulary, you just have to want to learn it and keep practicing. I did not get your code right the first time, but I kept trying. I am a noob compared to a lot of people on this forum.
 

Forum statistics

Threads
1,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top