Insert a Blank Row after each Record without VBA

Hello friends I am back again with a new excel trick for you to learn.  Today I am trying to explain a very simple way to add a blank row after each record in your data.  This trick is very special because without VBA it is the only method to insert a blank row to your data except doing it manually.

Let me first tell you a little bit about the objective that I am planning to achieve. See the image below:
I want to insert a blank row after each record in my data. So there should be blank row between "Chris Smalling" & "Fabio" similarly between "Jonny" & "Nemanja". Now the challenge is to do it without using VBA codes. So here you go with the solution.

Enter a series of numbers in the column next to your data.  You can also use "Fill Handle" to put the series of numbers if your data is large, this is an efficient way of putting numbers.
Once you have numbers next to your data select the series and copy it. Go to the last number in the series and below it paste the full series again.
Now select your complete data along with the series entered. Here the series has been entered in column 2 and now sort it in ascending order with the column containing your number series.
Now if you see your original data has got a blank row after each record. This is what we were trying to achieve with this trick.  I know this can be done with a very simple VBA code, for my super efficient VBA loving friends I am posting the code in comments below.

I hope we you all have gained something out of this post. Your suggestions and feedback are most welcome. Till then "HAPPY LEARNING"

35 comments:

  1. Sub Insert_Blank_Row()

    Application.ScreenUpdating = False
    Range("A1").Select

    Do Until IsEmpty(ActiveCell) = True
    Rows(ActiveCell.Row + 1).Insert
    ActiveCell.Offset(2, 0).Select
    Loop

    Application.ScreenUpdating = True

    End Sub

    ReplyDelete
  2. Sidhi bat No bakawas! Excellent!

    ReplyDelete
  3. Very nice trick. Thanks!

    ReplyDelete
  4. Hi..sir, I think this is nothing but smartness at real time practice. am I correct

    ReplyDelete
  5. Good one Prakash - Keep them coming!!

    ReplyDelete
  6. kwel stuff dude!!!!
    not good wid excel, but this is really nice...

    ReplyDelete
  7. So if we need 5 blank rows in between, we got to copy the number sequence 5 five times below each set and then sort, right?

    ReplyDelete
  8. OMG! Well was this trick two months ago? I had to learn VBA to do that because I had to do about 1500 records a week and I had to insert empty rolls. Urgh!!!! Now my job has gotten easier. Thanks!!!!

    Johnny

    ReplyDelete
  9. @Deepak: You're cent percent correct here dear!

    @Johny: I wish I would have posted it earlier, but the good thing out of it is that you know VBA now.

    @ALL: Thanx for your nice comments!

    ReplyDelete
  10. Very nice prakash , and also it would be a great favour if u comment ur vba code. Since there are many amatures who are keen to understand every line of vba codes.
    Thanks for Ur efforts an guidance up till now.
    Anticipating more..

    ReplyDelete
  11. @Krunal: The first comment of this post has code to do it using VBA. Do you want me to explain that to peeps?

    I think they are easy to understand for any programmer.

    "HAPPY LEARNING" Cheers!

    ReplyDelete
  12. Clever, but I'd change the row heights instead to keep the data contiguous for filtering, pivot tables, and other purposes.

    ReplyDelete
  13. excellent trick, looking forward for few more !!

    ReplyDelete
  14. Dear Prakash!
    This is very good stuff! This is real smartness for doing big things with less resources like lifting of a heavy weight with a small lever.

    About 12 years back I also solved a problem more or less with same trick. I will share it later.

    ReplyDelete
  15. Inserting rows is ok but how do you copy the records in the next row, example, row one has got data and using avobe trick we can insert new row, now we want to copy the data into the inserted rows for a range of records.

    ReplyDelete
  16. splendid....! I had managed to fetch all my SMS messages onto an excel file. But the data was cluttered. Your sorting technique was just what i needed. this helped me make my SMS file for my gf more readable.

    Thanks from both us.!

    ReplyDelete
  17. Really an excellent sharing by Mr.Prakash which has helped all of us doing our work a much faster.

    Thanks
    Arun

    ReplyDelete
  18. This helps sooo much...I would like to ask for one adjustment. How would you change the code to insert 5 blank rows intead of only 1? Thank you!!!!

    ReplyDelete
  19. Hi

    For inserting 5 blank rows after every cell value, you can simply paste the number series 5 times after the original one and then sort the series in ascending order.

    Thanks
    Anurag Rastogi

    ReplyDelete
  20. How would you do this if you have data from Column A to Column M that you want left together?

    ReplyDelete
  21. How would you do this if you have data from Column A to Column M that you want left together?

    doe jane L M B 9 xxxxx xxxxx xxxxx
    doe jane L M B 9 xxxxx xxxxx xxxxx
    doe jane L M B 9 xxxxx xxxxx xxxxx
    doe john l F B 9 xxxxx xxxxx xxxxx
    doe john l F B 9 xxxxx xxxxx xxxxx
    doe john l F B 9 xxxxx xxxxx xxxxx
    doe john l F B 9 xxxxx xxxxx xxxxx

    ReplyDelete
  22. great stuff, keep doing.

    ReplyDelete