Solved Insert rows above based cell content

September 10, 2015 at 12:54:01
Specs: Windows 7
I have a spread sheet that has 4 columns, and over 8500 rows. In column A every 90 or so rows, I have a title. All titles start with "MNEMONIC". I would like to insert 5 rows on top of each title without having to go thru 95 titles and doing insert 95X3. Is there a way to do this? I know very little about formulas and nothing at all about macros.

or I would like to make Each title print on a seperate page.

See More: Insert rows above based cell content

Report •

September 10, 2015 at 16:04:50
✔ Best Answer
Try this...
Sub Macro2()
'Activate A1 to make sure we capture correct firstAddress
'Search Column A for MNEMONIC string
    With Worksheets(1).Range("A:A")
      Set mn = .Find("MNEMONIC", LookIn:=xlValues, lookat:=xlPart)
        If Not mn Is Nothing Then
'Set firstAddress for first occurrence of MNEMONIC
         firstAddress = Worksheets(1).Range(mn.Address).Offset(5, 0).Address
'At each occurrence MNEMONIC, insert 5 rows
              Worksheets(1).Range("A" & mn.Row & ":A" & mn.Row + 4) _
                           .EntireRow.Insert 'Shift:=xlDown
'Find the next occurrence of MNEMONIC
                Set mn = .FindNext(mn)
'Loop until we are back the beginning
            Loop While Not mn Is Nothing And mn.Address <> firstAddress
        End If
    End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question