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"
Sub Insert_Blank_Row()
ReplyDeleteApplication.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
Excellent...
ReplyDeletegenius...
ReplyDeleteSidhi bat No bakawas! Excellent!
ReplyDeleteVery nice trick. Thanks!
ReplyDeleteHi..sir, I think this is nothing but smartness at real time practice. am I correct
ReplyDeleteGreat!!!!
ReplyDeleteGood one Prakash - Keep them coming!!
ReplyDeleteGreat one dear!! loved it..
ReplyDeletekwel stuff dude!!!!
ReplyDeletenot good wid excel, but this is really nice...
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?
ReplyDeleteOMG! 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!!!!
ReplyDeleteJohnny
@Deepak: You're cent percent correct here dear!
ReplyDelete@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!
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.
ReplyDeleteThanks for Ur efforts an guidance up till now.
Anticipating more..
@Krunal: The first comment of this post has code to do it using VBA. Do you want me to explain that to peeps?
ReplyDeleteI think they are easy to understand for any programmer.
"HAPPY LEARNING" Cheers!
Clever, but I'd change the row heights instead to keep the data contiguous for filtering, pivot tables, and other purposes.
ReplyDeleteexcellent trick, looking forward for few more !!
ReplyDeleteCool stuff.
ReplyDeleteDear Prakash!
ReplyDeleteThis 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.
THIS IS SO GOOD
ReplyDeleteexcellent
ReplyDeleteInserting 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.
ReplyDeletesplendid....! 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.
ReplyDeleteThanks from both us.!
Really an excellent sharing by Mr.Prakash which has helped all of us doing our work a much faster.
ReplyDeleteThanks
Arun
Awesome idea its helps...
ReplyDeleteThis 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!!!!
ReplyDeleteHi
ReplyDeleteFor 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
brilliant
ReplyDeleteHow would you do this if you have data from Column A to Column M that you want left together?
ReplyDeleteHow would you do this if you have data from Column A to Column M that you want left together?
ReplyDeletedoe 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
great stuff, keep doing.
ReplyDeletemany many thanks
ReplyDeleteSuperb..... Thanks a ton
ReplyDeleteexcellent!!!
ReplyDeleteMujhe pata tha
ReplyDeletethanks a ton
ReplyDeleteThanks
ReplyDelete