CODEWITHSUNDEEP
excelexcel-formulavba
MJS
MJS

Reputation: 53

How do I insert a row every three cells in a large spreadsheet

Hoping you can help an basic excel user please!

I have a file of around 2000 rows and I need to add a line/break after every third one. Is there a simple way of doing this please?

Your help and advice would be much appreciated.

Thanks

Upvotes: 0

Views: 7639

Answers (6)

MatthewD
MatthewD

Reputation: 6761

If you want to try some VBA here is a button click event that will do the insert on every third row. Let me know if you have any questions.

Private Sub CommandButton1_Click()
Dim ws As Excel.Worksheet
Dim lRow As Long
Dim lastRow As Long

    'Set the worksheet object to the sheet by name
    Set ws = Application.Sheets("Sheet1")

    'Set the row to start looping(inserting) rows at
    lRow = 4

    'Find the last row with a value in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    'Account for the amount of rows that will be inserted.
    lastRow = lastRow + (lastRow * 0.33)

    'Loop through the worksheet from the start row to the last row
    Do While lRow <= lastRow

        'Insert a row
        ws.Rows(lRow).EntireRow.Insert

    'Increment the row to insert at on the next pass of the loop
    lRow = lRow + 4
    Loop

End Sub

Upvotes: 4

user4039065
user4039065

Reputation:

Here is a VBA equivalent to the solution proposed by Eric K. above. The orientation assumes column header labels in row 1 that should be left alone.

Sub insBlankFourthRow()
    Debug.Print Timer
    With Worksheets("Sheet3")
        .Columns(1).Insert
        With .Cells(1, 1).CurrentRegion  '<~~ original CurrentRegion
            With .Resize(.Rows.Count - 1, 1).Offset(1, 0)
                .Cells(1, 1) = 1
                .Cells.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
            End With
            With .Resize(Int(.Rows.Count / 3) + 1, 1).Offset(.Rows.Count, 0)
                .Cells(1, 1) = 3.5
                .Cells.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=3
            End With
        End With
        '
        With .Cells(1, 1).CurrentRegion  '<~~ new expanded CurrentRegion
            .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes
        End With
        .Columns(1).Delete
    End With
    Debug.Print Timer
End Sub

tbh, ~2000 rows of data isn't that much to be worried about but 10× or 100× that amount of data will start to lag significantly when inserting rows individually or in a bulk non-contiguous orientation. A 'helper' column populated with a Range.DataSeries method (fastest way I know of populating a sequence) can be readily discarded once its purpose has been fulfilled.

            insert_blank_fourth_row

Running the above against 2500 rows of random data typical of the image took ⁸⁄₁₀₀ of a second. That time might be moderately improved with disabling the Application.ScreenUpdating property and similar overhead.

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Assuming data starts in A1, in B1 and copied down to suit (i.e. past the end of the cells populated in ColumnA): 

=IF(MOD(ROW(),4)=0,"",OFFSET(A$1,3*INT((ROW()-1)/4)+MOD(ROW(),4)-1,))

Upvotes: 0

Eric K.
Eric K.

Reputation: 834

a non vba way is create a new column and insert numbering

1
2
3
4
5
6
......

then for empty row, number as 3,6,9,.... (let say u have 2k records, duplicate it 2k) then sort by the number column, then remove the column

enter image description hereenter image description here

Upvotes: 2

brettdj
brettdj

Reputation: 55682

Quick way without VBA

  1. In a empty column add this formula, =IF(MOD(ROW(),3)=0,NA(),"") and copy down
  2. Press F5, Goto .... Special, Formulas Errors (selects every third row)
  3. Insert Rows

step 2 shown below

enter image description here

Upvotes: 6

gravity black
gravity black

Reputation: 656

If your content is the same, or repeating, for each row, you could open the file in any basic text editor and do the following:

  1. Highlight and copy the first three rows (including the third line break).
  2. Using find-and-replace (Opt-Cmd-F in TextEdit on Macs), copy that content into the 'find' field as well as the 'replace' field.
  3. Add a line break at the end of the content you pasted in the 'replace' field.
  4. Execute the find-and-replace action.

This should turn something like this:

item
item
item
item
item
item
item

...into this:

item
item
item

item
item
item

item

...and so on.

This definitely isn't the most elegant solution, but is one of the quickest/simplest I've seen without resorting to a text parsing script in bash, etc.

Upvotes: 0

Related Questions