Thread: automatically insert row

Hello forum, I have a huge problem with Excel and I have no idea how to solve it. Hmm, how do I explain this? Someone is giving me data pulled from a server in an Excel format. I have a column that has a series of entries. For example:

Blank
Entry 1
Entry 2
Entry 3
Entry 4
Entry 5

However, sometimes there's an entry or two missing. So it'll look like this:

Blank
Entry 1
Entry 2
Entry 4
Entry 5

Is there anyway to tell Excel to say "Hey there's an entry missing here," and have it insert a row with the missing entry value? I do not work in Excel ever but people around here do, in case someone has some advice. I already asked the guy if he could just server me the data formatted correctly, and -- of course -- he can't/won't. Thanks in advanced!!

Is the data as simple as that -- a single word followed by a single number?

Again -- is the data in column B as simple as that? Or exactly like that: GROUP followed by a 2-digit number?

Try this:

Code:

Sub GroupXX()
    Dim iRow        As Long
    Dim n1          As Long
    Dim n2          As Long

    iRow = Cells(Rows.Count, "B").End(xlUp).Row
    Do
        With Cells(iRow, "B")
            If UCase(.Text) Like "GROUP##" Then
                n1 = Right(.Text, 2)
                If UCase(.Offset(-1).Text) Like "GROUP##" Then
                    n2 = Right(.Offset(-1).Text, 2)
                    If n2 < n1 - 1 Then
                        Rows(iRow - 1).Copy
                        Rows(iRow).Insert
                        Cells(iRow, "B") = "GROUP" & Format(n1 - 1, "00")
                        n1 = n1 - 1
                        iRow = iRow + 1
                    End If
                End If
            End If
        End With
        iRow = iRow - 1
    Loop While iRow > 2
End Sub

Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel

Running a Macro
1. Do Alt+F8 to open the macro dialog
2. Select the macro name (here, GroupXX) from the dropdown list and press Run

Maybe if you post before and after examples, showing (in the after example) the inserted rows highlighted.

Please provide before and after examples of what you want.

Hello fishnfrogs,

Here is another macro to add in the missing rows with the group and sequence numbers. I have run this on the data you posted and it works. Let me know if I missed anything. This is little more complex than Shg's code, so I haven't added comments to explain what is happening. The complexity makes it a little more difficult to understand, but also provides a lot of flexibility in string manipulation.

I see from your profile you're a Tar Heel. I grew up in in the western side of the state in Asheville. Where are you?

Macro Code

Code:

Sub AddMissingRows()

  Dim Cell As Range
  Dim Cnt As Long
  Dim Group As String
  Dim I As Long
  Dim RegExp As Object
  Dim Rng As Range
  Dim RngEnd As Range
  Dim SeqX As Variant
  Dim State As String
  Dim Wks As Worksheet
  
   'Worksheet that holds the data
    Set Wks = Worksheets("Sheet2")
    
   'Start with "B2" to find the last data entry in column "B"
    Set Rng = Wks.Range("B2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
    
     'Create Regular Expression object for pattern matching, text search and text replace
      Set RegExp = CreateObject("VBScript.RegExp")
      RegExp.IgnoreCase = True
      RegExp.Pattern = "([a-z]+)(\d+)"
      
     'Step through the data cells one at a time
      For Each Cell In Rng
        If Cell = "" Then
           Cnt = 0
           State = Cell.Offset(0, -1)
        End If
       'Check if the cell contains a alpha characters followed by numbers
        If RegExp.Test(Cell) Then
          'Extract the group name
           Group = RegExp.Replace(Cell.Text, "$1")
          'Extract the sequece number
           SeqX = Val(RegExp.Replace(Cell.Text, "$2"))
            'Test if the sequence number
             If SeqX <> Cnt Then
               'Add the appropriate number of rows if the sequence number is off
                For I = Cnt To SeqX - 1
                  Cell.EntireRow.Insert Shift:=xlDown
                  Cell.Offset(-1, -1) = State
                  Cell.Offset(-1, 0) = Group & Format(I, "00")
                Next I
               'Extend the row by the number of new rows added
                Set Rng = Rng.Resize(RowSize:=Rng.Rows.Count + (SeqX - Cnt - 1))
             End If
           Cnt = Cnt + 1
        End If
      Next Cell
      
   'Release the object reference and the memory it used
    Set RegExp = Nothing
    
End Sub