Insert a blank row into every other row for selected list?

[paulwass]

I am selecting 20 rows, and I want to insert a blank row in between each of them. How can I do this?

Thanks,

P

(using excel 2000)

 

[Mark W.]

Re: Insert a blank row into every other row for selected lis

Rather than insert consider sorting blank rows among the original data (shown in yellow) below. Just number the rows and duplicate the numbers immediately below (shown in blue below). After sorting the on the column A:A which contains your numbers the results are shown in pink...

Book1
	A	B	C	D	E	F	G	H
1	1	cat
2	2	dog
3	3	mouse
4	1
5	2
6	3
7
8	1	cat
9	1
10	2	dog
11	2
12	3	mouse
13	3
14
Sheet1

 

[paulwass]

Re: Insert a blank row into every other row for selected lis

This is for existing data, and I didn't want to have to add in any more columns (or have my user do this).

 

[Mark W.]

Re: Insert a blank row into every other row for selected lis

You'll need to find someone to write a macro for you.

 

[paulwass]

Re: Insert a blank row into every other row for selected lis

Okay.. Thank you for the info!!

P

:D

 

[nevillestoke]

Re: Insert a blank row into every other row for selected lis

I needed similar code some time ago and used this. Make sure you save your work before you test this, but if you place your cursor at the start of the column you wish to check for data, this code will insert one row each time a non-blank cell is encountered. The code will only run for the first 20 rows of data.

Give it a try and hopefully it will be what you are looking for.

Sub insertrow()
' insertrow Macro

Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer

For count = 1 To 20
If activecell.Value <> "" Then
activecell.Offset(1, 0).Select
Range(activecell, activecell.Offset(0, 0)).EntireRow.Insert
activecell.Offset(1, 0).Select
For X = 1 To 1
Next X
Else
activecell.Offset(1, 0).Range("a1").Select
End If
Next count

End Sub

Neville.

 

[missytordrup]

IT WORKED! That is sooooo cool! you just saved me 2 days of work of doing it manually for about 12000 lines og data!

 

[mbski]

Re: Insert a blank row into every other row for selected lis

Hi

The macro you provided does what I need it to do....almost. It inserts a row BELOW any cell that contains data however, I need to insert a row ABOVE any cell that contains data. Would it be possible to get a tweak done to have the row inserted above instead of below? It would save me lots of effort of having to manually insert rows...

Thanks!

I needed similar code some time ago and used this. Make sure you save your work before you test this, but if you place your cursor at the start of the column you wish to check for data, this code will insert one row each time a non-blank cell is encountered. The code will only run for the first 20 rows of data.

Give it a try and hopefully it will be what you are looking for.

Sub insertrow()
' insertrow Macro

Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer

For count = 1 To 20
If activecell.Value <> "" Then
activecell.Offset(1, 0).Select
Range(activecell, activecell.Offset(0, 0)).EntireRow.Insert
activecell.Offset(1, 0).Select
For X = 1 To 1
Next X
Else
activecell.Offset(1, 0).Range("a1").Select
End If
Next count

End Sub

Neville.

 

[krishnap2w2]

Re: Insert a blank row into every other row for selected lis

I have encountered a similar problem and resolved it.


How to Insert a Blank Row After Every Other Row in Excel


Sometimes you may face a situation where in you are given thousands of rows of data and you need to create a blank row


after every other row in the excel sheet. In order to do it manually, it will consume lot of time. Learn the following


trick to do it in seconds.
1 Since there are 10 rows of data in the given example, write numbers from 1 to 10 in column D starting from row number


two.
2 Copy the range from D2:D11 and paste it in D12, so that the numbers from 1 to 10 repeat once again.
3 Select the range A2:D21.
4 Choose Custom Sort from Sort & Filter menu in Home tab.
5 Choose Column D in the Sort by menu, Values in Sort On menu and Smallest to Largest in the Order menu.
6 Delete the numbers in the column D.
7 Blanks rows have been successfully inserted after every other row in the given range.

 

[NewOrderFac33]

Re: Insert a blank row into every other row for selected lis

Good challenge!
I came up with the following:

Sub InsertRowsBefore()
    Dim MyCell As Range
    Dim SelectionHeight As Long
    Dim MyCount As Long


    Range("A2:A11").Select 'I used this for testing purposes

    SelectionHeight = Selection.Rows.Count
    'Allow for selection starting in Row 1
    If Selection.Cells(1).Row = 1 Then
        Selection.Cells(1).EntireRow.Insert
        Selection.Offset(1, 0).Resize(SelectionHeight, 1).Select
    Else
        Selection.Cells(1).Offset(-1, 0).EntireRow.Insert
    End If
    
    'Row 2 and subsequent
    SelectionHeight = Selection.Rows.Count
    Selection.Cells(1).Select
    
    For MyCount = 1 To SelectionHeight
        'MsgBox ("Current Row: " & Selection.Row)
        If Selection.Row = 3 Then
            Selection.Offset(1, 0).Resize(1, 1).Select
            Selection.Insert
        Else
            Selection.Offset(2, 0).Resize(1, 1).Select
            Selection.Offset(-1, 0).EntireRow.Insert
        End If
    Next
End Sub

It caters for data starting in Row 1 or any other row (I think!) and inserts a blank row (before) each row in the selection (although I don't really understand the difference between inserting rows before or after each row in a data range - isn't the end result surely the same?

Also, here's a shortened version of the code to insert a blank row AFTER each row in the selection.

Sub InsertRowsAfter()
    Dim MyCell As Range
    For Each MyCell In Selection
        If MyCell.Value <> "" Then
            MyCell.Offset(1, 0).EntireRow.Insert
        End If
    Next MyCell
End Sub

Hope this helps - happy New Year!

Pete