How do I insert a row at every change in number

Here is a macro that will insert a row at every change in value in a user selected column. I also included a statement specifying that the column be AZ, so use either the three statements for user choice or the one for fixed choice.

Sub ChangeOfNumber()
Dim cel As Range, rg As Range
Dim i As Long, n As Long

    'Choose either next 3 statements (user selects range) or the 4th one
On Error Resume Next
Set rg = Application.InputBox("Select the column or range that needs cells inserted", Type:=8)
On Error GoTo 0
'Set rg = ActiveSheet.Range("AZ:AZ")

If Not rg Is Nothing Then
    Application.ScreenUpdating = False
    If (rg.Rows.Count = 1) Or (rg.Rows.Count = rg.EntireColumn.Rows.Count) Then Set rg = Intersect(rg.Cells(1).EntireColumn, rg.Worksheet.UsedRange)
    Set rg = rg.Columns(1)
    n = rg.Rows.Count
    For i = n To 1 Step -1
        If rg.Cells(i) <> rg.Cells(i + 1) Then rg.Cells(i + 1).EntireRow.Insert
    Next
End If
End Sub

Select allOpen in new window

Many Thanks

You’re welcome and I’m glad I was able to help. You might also consider giving byundt some credit because if you delete rows 6 to 8 and uncomment row 9 in his code his result is the same as mine.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

I know you've already accepted an answer for this but there is an option that doesn't need VBA.

You could use the Subtotal wizard on the data tab to do this. In order to insert a Subtotal the wizard adds a row after each change in whichever column you specify. In addition you get the Outline grouping with buttons in the row header margin to expand or collapse the groups.

If you don't want to keep the subtotals created it is easy to delete them, use a filter on that column to show rows that include "Subtotal" or "Count" depending on what was specified in the Subtotal wizard. You can then select all the cells in one go and press delete key, the hidden rows will not be affected.