Automatically Insert Row

[Recursive]

Hi, I'm a novice user of Excel. I use it a lot, but for basic stuff. I have a 6000 line item spreadsheet I need to clean up. It's a sales order report and I need to insert a break after each order. The order number is displayed on each item of each order(each order has multiple lines)

Is there a way to do this easily? (w/o using macros?) Below is a screenshot of what the spreadsheet looks like if that helps. Thanks in advance!

 

[mumps]

I think you may need a macro to do what you want. If you're willing to try, do the following: hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro below into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub InsertRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim order As Range
    Dim foundOrder As Range
    Dim rngUniques As Range
    Sheets("Sheet1").Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("B3:B" & LastRow).SpecialCells(xlCellTypeVisible)
    Range("B1").AutoFilter
    For Each order In rngUniques
        With Sheets("Sheet1").Range("B:B")
            Set foundOrder = .Find(order, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not foundOrder Is Nothing Then
                foundOrder.Offset(1, 0).EntireRow.Insert
            End If
        End With
    Next order
    Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Recursive]

I think you may need a macro to do what you want. If you're willing to try, do the following: hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro below into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub InsertRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim order As Range
    Dim foundOrder As Range
    Dim rngUniques As Range
    Sheets("Sheet1").Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("B3:B" & LastRow).SpecialCells(xlCellTypeVisible)
    Range("B1").AutoFilter
    For Each order In rngUniques
        With Sheets("Sheet1").Range("B:B")
            Set foundOrder = .Find(order, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not foundOrder Is Nothing Then
                foundOrder.Offset(1, 0).EntireRow.Insert
            End If
        End With
    Next order
    Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Thanks for your reply. Unfortunately I've gotten some errors following your steps. Here's what happens... first I got the error, then I hit debug and that's what came up.

 

[mumps]

Do you have a sheet named "Sheet1"?

 

[Recursive]

Do you have a sheet named "Sheet1"?

Yes

 

[Fluff]

Judging by the image it's called TB March2018

 

[mumps]

@Fluff: Those are some "eagle eyes" you have. Thanks for picking up on that. @Recursive: Looking at your screen shot more carefully as Fluff did, you have a sheet named "TB March2018". The sheet name is the part in quotes. The first part (Sheet1) is the code name. Try this revised version:

Sub InsertRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("TB March2018").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim order As Range
    Dim foundOrder As Range
    Dim rngUniques As Range
    Sheets("TB March2018").Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
    Set rngUniques = Sheets("TB March2018").Range("B3:B" & LastRow).SpecialCells(xlCellTypeVisible)
    Sheets("TB March2018").Range("B1").AutoFilter
    For Each order In rngUniques
        With Sheets("TB March2018").Range("B:B")
            Set foundOrder = .Find(order, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not foundOrder Is Nothing Then
                foundOrder.Offset(1, 0).EntireRow.Insert
            End If
        End With
    Next order
    Sheets("TB March2018").Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Recursive]

@Fluff: Those are some "eagle eyes" you have. Thanks for picking up on that. @Recursive: Looking at your screen shot more carefully as Fluff did, you have a sheet named "TB March2018". The sheet name is the part in quotes. The first part (Sheet1) is the code name. Try this revised version:

Sub InsertRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("TB March2018").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim order As Range
    Dim foundOrder As Range
    Dim rngUniques As Range
    Sheets("TB March2018").Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
    Set rngUniques = Sheets("TB March2018").Range("B3:B" & LastRow).SpecialCells(xlCellTypeVisible)
    Sheets("TB March2018").Range("B1").AutoFilter
    For Each order In rngUniques
        With Sheets("TB March2018").Range("B:B")
            Set foundOrder = .Find(order, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not foundOrder Is Nothing Then
                foundOrder.Offset(1, 0).EntireRow.Insert
            End If
        End With
    Next order
    Sheets("TB March2018").Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

That did it! Thank you so much. I will be running these reports monthly and renaming them each time. In the future do I just need to change the text in the script to in all the spots where it says the spreadsheet title? Thanks again!

 

[Fluff]

Assuming the files are text files & therefore will only ever have 1 sheet, you could try

Sub InsertRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets(1).Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim order As Range
    Dim foundOrder As Range
    Dim rngUniques As Range
    Sheets(1).Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
    Set rngUniques = Sheets(1).Range("B3:B" & LastRow).SpecialCells(xlCellTypeVisible)
    Sheets(1).Range("B1").AutoFilter
    For Each order In rngUniques
        With Sheets(1).Range("B:B")
            Set foundOrder = .find(order, After:=.Cells(1), lookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not foundOrder Is Nothing Then
                foundOrder.Offset(1, 0).EntireRow.Insert
            End If
        End With
    Next order
    Sheets(1).Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[mumps]

You are very welcome. You just have to make sure that the sheet name in the code matches the actual sheet name in your workbook.