Write a Simple Macro in Excel and automate repetitive process

Write a Simple Macro in Excel and automate the repetitive process


In a today’s world, whatever may be the company size or field, Excel becomes the most useful/unavoidable application.  So in this article am going to share one of my real time experience which saved me huge amount of time in an excel work. Hope it will help to  save your time too.

Few days back, there was a need in our project, to insert the huge (around 30,000) records of data to database from the Excel.  But there was problem in the excel that, the data was not in the correct format.  So I used macro to correct the format

For Ex:


                          (Before Run Macro: Original/unformatted sample data from excel)


In the above snagit, just see the row no 3 & 4 in that, For us  to get the correct format, the description has to merge as a one values, unit and Rate value has to move to the previous row(ie)3rd row  and finally the entire 4th row has to deleted since it will became empty .

In the same criteria we had around 5000 rows so just think! If we do this manually how much time and effort it will take? So to avoid that, I wrote a macro and completed the process within a few seconds

Output -After Ran Macro: Sample Formatted data


Ok, Now am going to show the process, which  i did to get the above output, as a first step we need to enable the developer tab in the excel by default it won’t be available. So to enable just see the below snag its

Office button ->Excel Option->Popular


(check the box and then  you can see the new tab will be added to excel like the below snag it)


Next step is to click the visual basic icon and you will get the following screen:


Now Right click the Microsoft excel object folder and insert the module like below screen shot:



This is the place, where we need to write our logic or requirement


macro code
macro code


Code with Description for each line :


 Reference :

MSDN-Excel VBA Reference

MSDN-Refer to Sheets by Index Number


I have explained the code by providing the comments(in single quotes) for each line, so that it will be easy understandable . Hope it will be surely helpful ,kindly share your thoughts or feedbacks






459 total views, 1 views today


  1. We’re a group of volunteers and opening a brand new scheme in our community. Your site offered us with useful information to paintings on. You’ve performed a formidable job and our entire group can be thankful to you. dadcfdkkgcekadee

  2. Johng527

    Spot on with this writeup, I actually believe this website needs a great deal more attention. Ill probably be returning to read through more, thanks for the info! dffkffkedkee

Leave a Reply

Your email address will not be published. Required fields are marked *