Write a Simple Macro in Excel and automate repetitive process

Write a Simple Macro in Excel and automate the repetitive process

Background:

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:

macro_image1

                          (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

macro_image2

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

macro_image3

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

 macro_image4

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

macro_image5

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

 macro_image6

 

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

Conclusion:

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

Thanks

karthik

 

 

 

485 total views, 1 views today

7 Comments

  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 *