How to quickly insert blank /empty rows in Excel?

Most of us may face the problem in Excel that we need to insert a blank row between each of the existing rows, and all of us know how to insert a blank row manually. But inserting hundreds of blank rows will be a tedious task if you insert each row individually. Here are some quick tricks to help you solve this problem.

Insert blank rows with Sort function

Insert alternate blank rows with VBA code

Quickly insert specific numbers of blank rows with Kutools for Excel


arrow blue right bubble Insert blank rows with Sort function


This method is an easier but roundabout to insert blank rows between exiting rows. You can do as follows:

1. You need a blank column adjacent to your data. For example, I have a worksheet comprises A1:E9, you can use column F.

2. In cell F1 input the number 1, and input 2 in cell F2.

3. Select the number 1 and number 2, and double-click the fill handle, Excel will auto-fill the cells in column F.

4. Then copy this new column F (F1:F9), select the cell F10, and paste the auto-fill number from F1:F9. See screenshot:

doc-insert-blank-rows1

5. And then click Data > Sort, and a Sort Warning dialog box will pop out, select Expand the selection option, and click Sort… See screenshot:

6. And a Sort dialog box will appear, choose Column F from the Sort by dropdown list. See screenshot:

7. Click OK. Then delete the column F. And the new blank rows have been inserted in the exiting rows. See screenshot:

Note: If you would like to insert two or three blank rows between each row, you can copy the new auto-fill column two or three times to add the new rows.


arrow blue right bubble Insert alternate blank rows with VBA code

If the above way is a little complex, you can also use the VBA code to solve it.

1. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Sub InsertBlackRows()
'Updateby20131127
Dim Rng As Range
Dim WorkRng As Range
Dim FirstRow As Integer, xRows As Integer, xCols As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
FirstRow = WorkRng.Row
xRows = WorkRng.Rows.Count
xCols = WorkRng.Columns.Count
Application.ScreenUpdating = False
WorkRng.Cells(xRows, 1).Resize(1, xCols).Select
Do Until Selection.Row = FirstRow
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Offset(-1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub

2. Then click  doc-multiply-calculation-3 button to run the code, then a dialog is diaplayed on the screen for you to select a range to insert blank rows, see screenshot:

3. Click Ok, and a new blank row will be inserted between every two rows. See screenshots:


arrow blue right bubble Quickly insert specific numbers of blank rows with Kutools for Excel

The two methods all just refer to inserting blank rows between each rows, if I want to insert a blank row after every nth rows, how should I do?

The tool Insert blank rows & columns of Kutools for Excel can help you to do this quickly and easily.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

After installing Kutools for Excel, please do as this:

1. Select the range that you want to insert the blank rows.

2. Click Kutools > Insert > Insert Blank Rows & Columns…, see screenshot:

3. In the Insert Blank Rows & Columns dialog box, choose Blank rows from Insert type, and then specify the numbers into Interval of and Rows box that you need. See screenshot:

4. Then click OK. And the blank rows have been inserted after every two rows. See screenshots:

Note: the number in the Interval of and Rows can be adjusted to meet your need. For example, you can insert three, four, five… blank rows after every one, two, three… rows.

Click to know more about this Insert Blank Rows & Columns feature.

Free Download Kutools for Excel Now


Related articles:

Quickly insert blank column every other column

Insert multiple blank rows or columns at once

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink 0 maximus
Thank you. It was helpful.
2014-02-08 08:14 Reply Reply with quote Quote
Permalink 0 Evan
Worked perfectly. Thanks for the help.
2014-02-19 15:58 Reply Reply with quote Quote
Permalink -1 ruff
excelent...than ks a lot.
2014-02-20 07:41 Reply Reply with quote Quote
Permalink 0 g
Amazing! Was really simple and came out perfect! Thank you
2014-03-06 16:37 Reply Reply with quote Quote
Permalink 0 Benjy
It's what I need, many thanks!! Excellent!!
2014-04-15 09:25 Reply Reply with quote Quote
Permalink +1 Milind Joshi
Excellent stuff !!!!!!!!!!!!!!
2014-04-24 12:48 Reply Reply with quote Quote
Permalink 0 ajeem
very help full. thanks to you
2014-05-28 10:45 Reply Reply with quote Quote
Permalink 0 Fahim
I want to insert two rows in between data please help
2014-07-16 19:09 Reply Reply with quote Quote
Permalink +2 JoeShmoe234
Instead of doing just 2 1-9's do 3 1-9's and it will put 2 rows between data, using the first example.
2015-02-18 20:21 Reply Reply with quote Quote
Permalink 0 Ehtisham Ali
Perfect solution...work ed fine for me.thanks champ
2014-09-17 10:59 Reply Reply with quote Quote
Permalink -1 Shajtih
Success...
Thanks For the wonderful idea
2014-10-07 09:33 Reply Reply with quote Quote
Permalink -1 hema
You are the best excel site on the net
really you are the king or Excel
2014-10-14 11:59 Reply Reply with quote Quote
Permalink -1 Fresh
you save me of hours of work!!
2014-10-31 20:52 Reply Reply with quote Quote
Permalink -1 Mohan Kumar
Great!!! You saved hours of Work!!!
2014-11-12 09:33 Reply Reply with quote Quote
Permalink 0 Chris Featherby
Not an expert at Excel but found an easy way (for me). I added a column at A. In the first 3 rows of data typed 1, 3, 5 selected & expanded down the page. At the end of my data I used the blank cells in A & typed 2, 4, 6 & expanded this to equal the number of odd rows then sorted by column A smallest to largest. I was left with column A 1 - 250 & every other line was data / blank row.
2015-04-18 08:32 Reply Reply with quote Quote
Permalink 0 Vick
How to add blank row after every 9th row of the data, Eg blank row in 10th row, 20th row
2016-08-31 06:52 Reply Reply with quote Quote
Permalink 0 Srinivas
Really good one it has helped me and easy to follow,
2015-05-22 04:08 Reply Reply with quote Quote
Permalink 0 Komal Rana
Unable to get it correct...tried to follow the same steps but cant get my querry resolved...plz help me.dnt knw wts goin wrong
2015-08-03 09:34 Reply Reply with quote Quote
Permalink 0 Komal Rana
I got it nw but it doesnt wrk with different cell sizes....plz help in the same...
2015-08-03 09:56 Reply Reply with quote Quote
Permalink 0 Akash Swamy
Simply use Alt+H+I+R in a sequence manner to add a blank row immediatly!!!
2015-08-05 07:42 Reply Reply with quote Quote
Permalink 0 zulkifli
the best problem solve ever!!!
thanks dude!!!
the kutools work like what i want to do..thank you so much!!
terima kasih.. :D
2015-09-14 09:27 Reply Reply with quote Quote
Permalink 0 Shrikant
Here is the Answer what I
want
Thanks
2015-10-04 19:20 Reply Reply with quote Quote
Permalink 0 Shrikant
Thank that's good trick what I want
2015-10-04 19:22 Reply Reply with quote Quote
Permalink 0 Arshad
Is there any formula which automatically add row once applied. For example
My data are includes of 10 rows and the sum of that 10 figures are in 12th row. Now if I add new figure in 11th row the the Total should be shifted to the 13 row.
2015-10-13 17:22 Reply Reply with quote Quote
Permalink 0 Mayank Jain
Thanks for publish these important Tricks for solving Critical problems.
2015-11-04 07:45 Reply Reply with quote Quote
Permalink +1 Marco
This is EXACTLY what I was looking for, simple, brilliant, something I never would have thought of. (1st method)

Thank you! (saved me at least 3 hrs of work) :lol:
2016-01-15 01:41 Reply Reply with quote Quote
Permalink +1 Teerath Singh
the best problem solve ever!!!
thanks dude!!!
the kutools work like what i want to do..thank you so much!!
2016-01-23 12:33 Reply Reply with quote Quote
Permalink 0 Osman
Siperb. Thanks . u r the best...!!
2016-03-05 17:26 Reply Reply with quote Quote
Permalink 0 Luis
It worked, it did the trick for me. Thanks!
2016-05-12 13:08 Reply Reply with quote Quote
Permalink 0 rj-uk
Siperb. Thanks . it worked...!!
2016-06-02 17:14 Reply Reply with quote Quote
Permalink 0 Gitty Einhorn
Thank you!!!!!!!!! So brilliant! Just saved me 10 min of inserting rows!
2016-06-14 17:40 Reply Reply with quote Quote
Permalink 0 JASPER
GOOD STUFF! Saved me lots of time
2016-10-25 16:29 Reply Reply with quote Quote
Permalink 0 fahad
very good its superb
2017-01-28 10:32 Reply Reply with quote Quote
Permalink 0 Rob
Can the VBA code be changed so that you do not need to select the range you want sorted, but instead is a specific range. For example I always want the range to be (S5:S500) I would like to run this code as part of a macro and do to the selection application this isn't working. This code works faster by far than other code ive tried.
2017-02-01 15:14 Reply Reply with quote Quote
Permalink 0 srivatsan guru
The sort option worked, sometimes we learn all advanced and tuff things but we tend to forget the simple ones.. Thanks a lot
2017-04-02 17:32 Reply Reply with quote Quote

Add comment


Security code
Refresh