Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Kutools for Excel 21.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 12.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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


Quickly insert specific numbers of blank rows to a range in Excel:

The Insert Blank Rows and Columns utility of Kutools for Excel can help you quickly insert certain numbers of blank rows or columns to a range in Excel. See the related opertions as below screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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:

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 number 1 from the Sort by dropdown list and then click the OK button. See screenshot:

Then 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.


Insert alternate blank rows with VBA code

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

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for applications window, then click Insert > Module and input the following code into the Module:

VBA code: Insert blank rows in Excel

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 or press the F5 key to run the code. In the popping up Kutoos for Excel dialog box, please select the range you will insert blank rows, and then click the OK button. See screenshot:

Then a new blank row has been inserted between every two rows. See screenshot:


Quickly insert specific numbers of blank rows with Kutools for Excel

The above two methods just refer to inserting blank rows between each rows, for inserting a blank row or multiple rows after every nth rows, how should you do? Here I recommend the Insert Blank Rows & Columns utility of Kutools for Excel. This uility can help to quickly insert specific numbers of blank rows after every nth rows into a certain range. Please do as follows.

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

1. Select the range that you want to insert the blank rows, and click Kutools > Insert > Insert Blank Rows & Columns. See screenshot:

2. In the Insert Blank Rows & Columns dialog box, choose Blank rows in the Insert type section, specify the numbers of interval rows you will insert blank rows based on and number of rows you want to insert, and then click the OK button. See screenshot:

Then you can see certain 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.

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel


Quickly insert specific numbers of blank rows with Kutools for Excel


Related articles


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Francis · 1 years ago
    Thanks so much for this. Cheers.
  • To post as a guest, your comment is unpublished.
    Peter · 1 years ago
    So I'm running into a situation where a have a couple hundred rows of information. Id like to create a space between specific data. example. I have six rows with "CH-01" in it and then two rows with CH-02- and then three rows of CH-03. What I want to happen is create a blank row between the change of numbers. However I don't have the time or energy to do this for each individual change. Please if anyone has a solution for this problem i'd be so happy!
    • To post as a guest, your comment is unpublished.
      Ken · 1 years ago
      I have the same problem too, have you figured out how to solve this?
  • To post as a guest, your comment is unpublished.
    shaifali · 1 years ago
    wonderful! it worked
  • To post as a guest, your comment is unpublished.
    ahmad · 1 years ago
    thankssssss a lot you are an awesome people.
  • To post as a guest, your comment is unpublished.
    Willi · 1 years ago
    Thaaannkksss its work like magic
  • To post as a guest, your comment is unpublished.
    MDC · 2 years ago
    The sort method worked like a charm. Thanks for saving me time coding
  • To post as a guest, your comment is unpublished.
    qwer · 2 years ago
    i'm not religious or anything, but you are god. ;-)
  • To post as a guest, your comment is unpublished.
    srivatsan guru · 2 years ago
    The sort option worked, sometimes we learn all advanced and tuff things but we tend to forget the simple ones.. Thanks a lot
  • To post as a guest, your comment is unpublished.
    Rob · 2 years ago
    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.
  • To post as a guest, your comment is unpublished.
    fahad · 2 years ago
    very good its superb
  • To post as a guest, your comment is unpublished.
    JASPER · 3 years ago
    GOOD STUFF! Saved me lots of time
  • To post as a guest, your comment is unpublished.
    Gitty Einhorn · 3 years ago
    Thank you!!!!!!!!! So brilliant! Just saved me 10 min of inserting rows!
  • To post as a guest, your comment is unpublished.
    rj-uk · 3 years ago
    Siperb. Thanks . it worked...!!
  • To post as a guest, your comment is unpublished.
    Luis · 3 years ago
    It worked, it did the trick for me. Thanks!
  • To post as a guest, your comment is unpublished.
    Osman · 3 years ago
    Siperb. Thanks . u r the best...!!
  • To post as a guest, your comment is unpublished.
    Teerath Singh · 3 years ago
    the best problem solve ever!!!
    thanks dude!!!
    the kutools work like what i want to do..thank you so much!!
  • To post as a guest, your comment is unpublished.
    Marco · 3 years ago
    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:
  • To post as a guest, your comment is unpublished.
    Mayank Jain · 4 years ago
    Thanks for publish these important Tricks for solving Critical problems.
  • To post as a guest, your comment is unpublished.
    Arshad · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Shrikant · 4 years ago
    Thank that's good trick what I want
  • To post as a guest, your comment is unpublished.
    Shrikant · 4 years ago
    Here is the Answer what I
    want
    Thanks
  • To post as a guest, your comment is unpublished.
    zulkifli · 4 years ago
    the best problem solve ever!!!
    thanks dude!!!
    the kutools work like what i want to do..thank you so much!!
    terima kasih.. :D
  • To post as a guest, your comment is unpublished.
    Akash Swamy · 4 years ago
    Simply use Alt+H+I+R in a sequence manner to add a blank row immediatly!!!
  • To post as a guest, your comment is unpublished.
    Komal Rana · 4 years ago
    I got it nw but it doesnt wrk with different cell sizes....plz help in the same...
  • To post as a guest, your comment is unpublished.
    Komal Rana · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Srinivas · 4 years ago
    Really good one it has helped me and easy to follow,
  • To post as a guest, your comment is unpublished.
    Chris Featherby · 4 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Vick · 3 years ago
      How to add blank row after every 9th row of the data, Eg blank row in 10th row, 20th row
  • To post as a guest, your comment is unpublished.
    Mohan Kumar · 5 years ago
    Great!!! You saved hours of Work!!!
  • To post as a guest, your comment is unpublished.
    Fresh · 5 years ago
    you save me of hours of work!!
  • To post as a guest, your comment is unpublished.
    hema · 5 years ago
    You are the best excel site on the net
    really you are the king or Excel
  • To post as a guest, your comment is unpublished.
    Shajtih · 5 years ago
    Success...
    Thanks For the wonderful idea
  • To post as a guest, your comment is unpublished.
    Ehtisham Ali · 5 years ago
    Perfect solution...worked fine for me.thanks champ
  • To post as a guest, your comment is unpublished.
    Fahim · 5 years ago
    I want to insert two rows in between data please help
    • To post as a guest, your comment is unpublished.
      JoeShmoe234 · 4 years ago
      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.
  • To post as a guest, your comment is unpublished.
    ajeem · 5 years ago
    very help full. thanks to you
  • To post as a guest, your comment is unpublished.
    Milind Joshi · 5 years ago
    Excellent stuff !!!!!!!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Benjy · 5 years ago
    It's what I need, many thanks!! Excellent!!
  • To post as a guest, your comment is unpublished.
    g · 5 years ago
    Amazing! Was really simple and came out perfect! Thank you
  • To post as a guest, your comment is unpublished.
    ruff · 5 years ago
    excelent...thanks a lot.
  • To post as a guest, your comment is unpublished.
    Evan · 5 years ago
    Worked perfectly. Thanks for the help.
  • To post as a guest, your comment is unpublished.
    maximus · 5 years ago
    Thank you. It was helpful.

Feature Tutorials